[SOUND] In the previous video, we saw how we could use the rather powerful lookup function called the OFFSET function. But we didn't really get a look at the width and height arguments. So that's what we're going to do now. The first thing Alex wants to do is automate the process of pulling through the appropriate lending information for the chosen Loan option. We have a selection of Loan options over here and if he selects Advanced Premium, he wants to see this information pulled through into the Loan Schedule. He has set up a Combo Box to select the loan option, but at the moment it is not actually doing anything. We're going to let it how we create these Combo Box. And then how we can use the OFFSET function to pull through the current information. To get rid of this one, I'm going to right click and cut. To create a Combo Box, we come to the Developer tab. If you don't see the Developer tab, go to the tool box for this week for instructions on how to find it. On the Developer tab is an INSERT button, which allows us to insert a variety of Form Controls. And the one we want in this case is Combo Box. I'm going to choose Combo Box and just draw one here. Normally I would cover this cell but I'd like you to be able to see what's going on, so I'm going to shy off off the edge. To populate the box, we're going to need to right click on it and choose Format Control. First, we're going to supply an Input Range. In other words, where it gets the values from. We're going to come to Loan options, and we're going to select the Loan Offerings. We then have to supply a Cell Link. When they choose one of those options, rather than returning the chosen option it's going to return the number in which it found it in the list. So if they choose the first one we'll get the number 1. We need to specify where that number goes and we're using H4, so I'm going to click into H4 and then say OK. If I come back to my control I'm just going to click away for a moment, click the drop down, choose Stater Plus. You see here we're getting a 1, Advanced Premium we're getting a 2, okay. I'm just going to go back to 1 for now. The next step is to use the value in H4 as an offset to pull through the appropriate loan information. Let's select these values and delete them. And now, with those cells still highlighted, because I'm going to use the OFFSET to get back multiple values, I'm going to type =OF and Tab. My reference is going to be in the Loan Options tab, I'll have to think about this. I want to offset by one to get Big Banks by two to get United Co. So my reference needs to be B4 and then I type a ,. The amount I want to offset by in terms of my rows is the option chosen from my list. I'm going to come back to Loan Schedule and click in H4. I don't actually have a column OFFSET, so that can stay 0. And the required height is one, so I could just type a comma but I'll put that in, and the width now this is the important part, is 3. Close the brackets, that is the OFFSET done. But the data we're pulling through is 3 columns, and 1 row. Where I'm pulling it to is 3 rows, and 1 column. So I'm going to need to add another function on to the outside of it. You may remember this one, it's the TRANSPOSE function and of course I'm dealing with a raise here. I'm returning multiple values, so don't forget Control+Shift+Enter. And there's the information coming through. And if I change this from Starter Plus to Advanced Premium, it pulls through the appropriate information, and the whole loan updates. Okay we'll go back to Starter Plus and that was one useful trick we can use with an OFFSET function. If you have worksheets or ranges within your worksheet that look very similar. And you want to be able to pull through different options you can use the OFFSET function to do it. Another thing the OFFSET function is often used for is creating dynamic ranges. You may want a dynamic range because the user may want to select different areas of a range to look at. Or it may be that the data is going to be added to the range over time, and for some reason, you can't use a table. In this situation, we wanted to create a dynamic chart. We have a chart set up here. And it has been put in to pull back the data for 20 years. If I scroll down, you can see, at the moment, it's pulling all the values through, which is fine. But when I update the term to 22 years for example, if I click back on the chart and scroll down, it's not picking up all the data. That's really not good enough. These cells under here actually are not empty. They contain formulas. Depending on the loan term, this will extend up to 26 years worth. It makes my range a little tricky to work with because I'm not just looking for an empty cell. I'm actually looking for a cell that's specifically contains the value 0. What I'm going to do through, to make this work, is create a dynamic range. I'll put the term back to 20 years, so we're all looking at the same figures here. They way we create a dynamic range usually, is using a named range, because charts can't refer to OFFSET functions. To type this kind of calculation directly into the name manager is a little tricky. So although it's not going to give us a sensible answer, just to make it easier to type we're going to put it directly to the work book and then we'll copy paste it to the name manager. The first range I'm concerned about is my due date range. And I'm going to type =OFFSET to create a dynamic range. The reference is the first date. But don't forget to put the $ on and then comma. The number of rows we want to offset is 0. And the number of columns we want to offset is 0. And actually, those are defaults, you don´t have to put the zeros, you can just type the comma. The height, that is of concern for me, that´s what´s going to vary. And I'm going to use a COUNTIFS to work out how high it should be. We're just going to put in COUNTIFS, and we're going to check the due date range, all the way from the top down, to 329. Again, we do need to make that absolute. And we're going to check that it's in fact greater than 0. Then, we close the brackets from the COUNTIFS. Close the brackets from the OFFSET and enter. That has returned the value error. Which is obviously not very helpful at this stage. But, if you wanted to see what values it was really returning, if you go back into the formula and press F9, these are the actual values. We don't want to keep the values. We want to keep the formulas. So, I'm just going to press Escape. Let's get back into the formula and we're going to copy it, the whole thing. Then we're going to come to the Name Manager. On the Formulas tab click Name Manager, and we're going to add a New Name. We're going to call this Chart_Dates. We're then going to replace whatever is in the Refers to, with equals. And we're going to put in our OFFSET function. If you can't see the whole thing don't use the arrow keys because that will mess up your formula. But you can make it a little bit wider and drag with your mouse, okay. There is our dynamic name range and we're going to click OK. We'll also need one for the interest and one for the principal. Those are the two lines we are charting. Let's just quickly do another one. I'm going to click New. This one is going to be chart interest. And what I'm going to do is paste exactly what I had before. And then I'm just going to make some tiny adjustments. To use the arrow keys press F2, watch the bottom left corner of the screen and you will see Enter change to Edit. Now you can use the arrows to move around the formula, just be aware you need to be in edit mode to use the arrow keys. The interest is in column F. Wherever I have a C, I'm just going to replace it with an F. That is my Chart interest and OK. I've already set up the Chart Principle, just to save us a little bit of time. That's all the named ranges ready to go. We can now close the Name Manager. We need to update the chart to use those dynamic ranges, rather than the fixed ranges it's currently using. I'm going to click into my chart, come up to my Chart Tools design tab, and I'm going to click Select Data. The first thing I'm going to change is my access labels on my dates. I'm going to do that by clicking on Edit. And then, where it says loan schedule exclamation mark, you have to be a little careful. We don't actually want to get rid of the worksheet name, so we're going to delete everything after the exclamation mark, but keep the worksheet name and the exclamation mark You can either type the name of the dynamic range or even easier, if you press the F3 key, that brings up all the named ranges. The one we're looking for is Chart_Dates. Double click that and say okay. That's the day it's corrected. I'm going to do exactly the same for Interest and Principal click on Interest, click Edit. Don't worry about changing the name, that's fine. We just need to change the range. Same thing, delete everything after theexclamation mark. F3 and this is going to be Chart Interest. Then we do the same for Chart Principal. Now say OK and that should have updated the chart to use these dynamic ranges. Which means when we change the loan term, the chart should automatically update. Let's update this to a 26 year loan and see what happens. We did see the charts update in terms of the values, but we want to make sure it's actually picking up all the new values as well. Let's just scroll right down and there you go. We now have a dynamic chart referring to a dynamic range and that was just one of the many examples you could apply dynamic ranges to. So, there was some really interesting uses for the OFFSET function. But, we've only given you just an introduction. Make sure you get in there, have a play, and find out what you can do with this great function. [SOUND]
