Video instructions and help with filling out and completing Who Form 1094 B Index

Instructions and Help about Who Form 1094 B Index

Welcome to youtubers love excel number 119 hey if you want to download this workbook and follow along click on my YouTube channel then click on my College website link and you can download the workbook youtubers of Excel 112 120 hey this youtuber asked about an exact video he tried to do Excel named trick number 7 in direct function and he had a problem again I always have notes up at the top and videos that refer you to other videos for further explanation now here's what we tried to do in that video we were using the inner sector operator space let's just show you how the Interceptor operator works if I was to say equals this right here and then a space and then this right here that's showing up in white you can't see that there's an equal sign a range of space and range by putting a space it says please find the intersection of the two now notice there's the blue there and there's the green the dancing ants right there so it should return that number 3 I'm gonna ctrl or alt e a F to get rid of all the for me I had some bad formatting there I went to use the keyboard circuit from earlier versions but there's the eraser and then clear formats because that was annoying I was getting in the way so if I double click this or hit the f2 key you can see there's blue and green that's the intersect operator well what he tried to do here was the full video now watch this we're gonna highlight this whole range here I'm going to use our name trick because we want to control Z I want to name this column time this column aids this column name and each one of these rows here this person's name there's a very quick way to do it in Excel you control shift f3 and that allows us to create names from selection top row and left column click OK watch this if I highlight right here you can see that oh wait a second it didn't do it right Nicole Bogdan it put a underscore instead of using that name you got it you can't have spaces when you name things so Excel was very polite there and put it in for you and that ultimately was the problem this youtuber had and will figure out how to do with that if I highlight this whole column right here I can see its age if I highlight this whole column its time and what he wanted to do is we want to create a formula here in this cell that would always allow him to we have data validation here select from this list of names right and have time and he wanted a formula here that would look to that suppose a name and this name and right here it would find this value right here which means intersection of that and this alright so let's see and this is exactly what we did in the name trick number seven we said equals in direct and indirect function takes a cell reference or a name as text and converts it to the actual range because watch this if we said equals this space this even though we named that whole range right there this formula has no way of knowing that that tima it t that time that word time is the actual named range so the way you deal with that is use the indirect function watch this and direct so by putting the indirect about that around that cell reference there that will tell it yeah it's the word time but now convert it to the actual named range and then we do the same here and then hit enter now so far if you're watching that name trick number seven video this would work except for one thing that series of characters is not the same as the name that we have up in this range if you go up here and highlight this you can see it's got an underscore so we need to deal with this fact now here's how we do it I'm going to hit f2 and there's a function called substitute now there's a couple ways you could solve this you could go through this and just remove the spaces and rename them you could also put underscores in there but what if you had a huge list coming in from some database you wouldn't want to do that you want to use those names and this name right here well there's a way we can change if I highlight this here and hit my f9 key you can see that it's thought Excel sees this as the text razza van loose I'm terrible at foreign languages but you can see that there's a space there and we don't we're gonna use this substitute function the substitute function takes a text string and you tell it hey look for the space and please substitute that space with an underscore I'm going to ctrl Z so right around that b5 I'm gonna type substitute and then I'm gonna have to put a closed parenthesis right there but I want the substitute substitute the text is whatever's there comma to get to the old text the old text is a space and watch this you put double quote space double quote so far with the substitute we said look here find that bad text we don't want a space now we have to put a comma and the new text we want is in quotes underscore in quotes and there you have it if you were to highlight this substitute right here and hit your f9 key to see what it evaluates to no way that's exactly right and it's text what the indirect function does is