Video instructions and help with filling out and completing Fill Form 2220 Consecutive

Instructions and Help about Fill Form 2220 Consecutive

Welcome to EXO magic trick number 756 hey if you want to download this workbook EXO magic trick 756 to 758 click on the link directly below the video hey this video 756 and 750 6.5 there's going to be two of them are going to be two parts the basic situation is we have a start date here we're going to do some conditional adding in the second video but each day of the month is represented by a sheet that has the name one two three four the third day four for the fourth day so the whole workbook had filled up be filled up with thirty one of these if you wanted I have just seven to make this video short and easy okay so the trick is the first one is we want to be able to type in a start date and have Auto populate the first of the month the second of the month the actual serial date here so how are we going to do that well this whole trick we type in a date here and the whole trick is going to be predicated on the fact that we've named the sheets one two three four five six seven all the way to 31 so we could use date math if we could somehow extract these sheet names on this sheet we could just say hey look at this date and in this case it would be add the sheet name - one which because for this is the first of the month for here we could say hey is there a way to look at this date add the sheet name and subtract one and that would add one and give us our date and that would Auto populate we could have the same formula in each cell and automatically Auto populate the dates anytime we put in a new starting date so we have to figure out how to extract a sheet name let's come up here there's a great function called cell now cell can do a number of things we want to look at file name so I'm going to double click this in earlier versions you have to type it out double quotes file name double quotes comma and be sure to put the reference a one that just anchors this formula and actually will make it work correctly because if you don't anchor it then you can get the same sheet name on all of the sheets instead of what we want a sheet name here sheet name here sheet name here all right so we can enter this here oh and we see gives us the whole path the actual location of the file and oh there's the workbook name and there's finally the sheet name at the end now I made this one easy it's just one two three no spaces no nothing so we don't need our normal sheet with single quotes and things like that well look at that the numbers always going to be after what a square bracket so we could use that pattern to help us extract just everything after the closed square bracket now I'm actually going to use the replace function I'm going to say an replace you could say start at the first character and go all the way to whatever location this square bracket is and I'm going to replace it with nothing and every the only thing left will be the actual number so let's see how to do this now I'm going to actually going to have to use this little piece twice so I'm going to copy it I'm going to say replace what's the old text that right there comma old text start number I want to start at 1 comma and I want to go from 1 all the way to that low the location of that square bracket so now I need a function that will tell me the location of that square bracket and that is search fine text it is a closed square bracket in double quotes comma within what well within what I'm going to control V that's that cell close parenthesis then it's going to ask me did I do that right now that's the number of characters right there the search is the number of characters to go from the first character comma and then the new text I want to replace all of that with double quote blank closed parenthesis and that gives us a 1 now notice it's aligned which direction to the left its text now by itself that can cause a problem but any operation adding double negative dividing etc on a number that's text will convert it to to a number so now we need to create so our operation is going to be hey look at that beginning sheet and then add this and so that the fact that we're adding is the operation that will convert it back to a number so now on this sheet I'm on the first one equals click on 7 56 I'm doing a sheet reference I click on that I can see that right there I'm just going to hit control enter right now it gives me Oh so the first day in 2011 is Saturday right okay so I need to then do what add this and since I'm always going to be one day off right here I don't really want to add one I'm going to subtract one all right so now we need to put these formulas into these sheets over here why don't we do a little trick here we're going to drill through I'm going to highlight both of these 1 2 3 and the template is the same on each sheet so no problem I'm selecting sheet 1 I'm going to highlight these ctrl C the active cell is right there I'm going