Print Motley Formula with airSlate SignNow

Print signatory formula

welcome to Excel magic trick number 215 hey if you want to download this workbook and follow along click on my youtube channel then click on my College up site link you can download the workbook Excel Maastricht 215 to 219 hey this trick 215 is based on at magic trick 213 in that trick we had a little data set we created a unique identifier and we had our data extracted over here so if I changed this the extracted records would change not only that but we had it set up as a table so when we add a new records to the bottom it would update now here's a problem what is since that this range of data is always changing how do we set a print area in essence we want a dynamic print area right now we'd like to print out that but if we change the criteria here we then want it to change to this well first before we see how to do it dynamically let's just see how to set print area now in 2007 you go to page layout and then print area and then set print area in 2003 you go to the file menu and then print area set print area and there you go now we have this I'm going to use the keyboard shortcut control f2 to look at print preview and you can see it got those values now I'm going to click escape print preview is in the file menu or in the orb now let's change the criteria and what happens whoops okay so we have all these blanks so now if we control f2 we can see we have some blanks so how do we do this well let's go ahead and see if we can figure out a way to do this we're going to still use that print set print area in fact let's go and see that when you set a print area it's actually a name now names in 2007 or under formula names define a manager in 2003 it's insert and then the keyboard shortcut is ctrl f3 and there it is you can see there's a print area so what we're going to do is we're going to create a named range that's a formula that will pick up dynamically the the data and the headers and footers and then we'll replace that print area name with our dynamic name all right how are we going to do this let's use our keyboard shortcut ctrl f3 and we're actually going to create a new name and we're going to name it something smart like print area I'm going to say offset because we're going to use the offset function and then I'm going to come down here notice it could be a workbook or a worksheet level a scope we want it for the whole workbook comments uh dynamic range for printing extracted records alright we're going to use the offset function offset equals offset now I've done lots of other videos on this the offset function will define a range for us now there's five arguments here the first argument to the offset and I'm going to click this right here so we can collapse down the first argument is where is the upper leftmost cell where this data set starts so it's going to be G 22 comma the second argument is from that starting point do you want to go up down or up any no we want to stand g20 Tucson the type of 0 comma the third argument is from this position do you want to go to the left or right any left would be negative right would be positive positive columns no we want to stay in G 22 so I'm going to put a 0 comma the next argument is how tall is it now here's our trick we're going to see count if open parentheses and what do we want to count well we're going to cau this whole rain actually will do this range right here because these are numbers if you remember from the last video these were numbers these were texts these are text rings but we'll do numbers right here we want to count this whole range comma and what do we want to count oh our our criteria close parenthesis so right now this will give us two now that won't work because what we want is 3 right 1 2 3 so we have to add 1 here comma and since our columns are always the same 1 2 3 4 we could put a 4 here if it was dynamic we could use something similar to that close parenthesis now I'm going to actually copy this because I know that's hard to see right there and I'm going to uncollapse and then click OK and watch this once you have a formula here you can then check to see if it's working white by clicking the collapse button and there it is it looks like it's working now let's click on collapse and then close let me just put this formula right here and watch when you hit enter it gives you a value R because it's not really meant to do that it's defining a range right there but there's the formula so if you want to hit pause and see that I also have it printed up here but that's the formula here's the starting point we're going down and over zero rows and columns we're counting we get to we add one right here so we get one two three and finally there's four calls so it defines a range I'm going to click escape I'm going to click delete now let's see if this works well right now it won't work because the print area is still the print area so let's go back to control f3 name manager or define names and we want to click on our print area and notice this is just a define name so we come down here we can delete it and we can use our keyboard shortcut f3 to paste a name and there it is I'm going to just double click that or you could just type equals and then type a name now so this is a name here but now we're inserting this name into another name is that is that okay does that work you bet it does I'm going to click close it's going to ask me do you want to save it I'm going to say yes oh it's already looking good look at that you can see the print area I'm going to control f2 to look at print preview and sure enough that looks like it works just fine escape now let's change the criteria this is so exciting I'm going to change it to zero zero it looks like it worked the dotted line which means set print area seems to work I'm going to control f2 sure enough that work so that is a click escape here that's how to create a dynamic print area based on our earlier formula all right we'll see you next trick