[SOUND] This week, Alex is going to be helping a real estate agent put together a workbook to respond to customer queries more efficiently. We already have the sales for August, September, and October in separate worksheets, and each of these are in a table. And these tables have been named using a convention that stipulates Sales_ and the name of the month. Alex has also created a Get Answers worksheet. And this is where he is going to retrieve the answers that customers most often ask. The first thing he needs to be able to do is to retrieve the correct Agent name and Contact number for the selected county. And to do this, he is going to use Excel's INDIRECT function. The INDIRECT function is a handy little lookup function. And it works slightly differently to most lookup functions in that it uses an indirect reference. What that means is that indirect refers to the address of the cell rather than the cell itself. Let's have a look at an example. A direct reference would go straight to G3, but the indirect is actually going to refer to C12, which is then going to return the value from G3. Let's see it, so we're going to type =IND and choose INDIRECT, and I'm going to click into cell C12. But when I click Enter, you'll see I don't get the value in C12, I get the value in G3. You may have noticed that INDIRECT actually allows for two arguments. The second one is optional, but it allows you to specify the type of referencing you are using. By default, it uses A1 referencing, but you also have the option to use R1C1 referencing. Let's have a look at an example how that would work. So in this cell, I have an R1C1 reference, which actually refers to row 3 column 8, which is the contact number for Buckinghamshire. We're going to use an INDIRECT function to retrieve that information. So =INDIRECT, click on the cell that contains the address. And this time, we need to specify that we are using R1C1 style, which we do with a FALSE and then Enter. Now those were just simple examples to make it clear how the INDIRECT function works. That's not typically how we would solve it, as we won't usually have the addresses sitting in cells like this. So we're actually going to get rid of that and we're going to look at a more typical example. The INDIRECT function will also allow you to construct the reference using a combination of text, cell references, and our formulas. And that's what we're going to do now. So I'm going to type =INDIRECT. My reference text is going to be made up of a combination of column G and whichever row matches Buckinghamshire. So I'm going to do an ampersand, and I'm then going to use a MATCH function. I'm going to look for Buckinghamshire in my county list, and this is an exact match. Close both the brackets, and that has worked. And we could use a very similar formula to get the contact number. So I'm just going to copy that, and we're going to paste it into Contact Number, but then replace the G with an H. INDIRECT is also very powerful when combined with Named Ranges. The next thing we want to do is calculate our total cells for whichever month has been selected in C5. Alex has made this very simple by naming the cell's columns in each of the different worksheets by the name of the month. And we can now use an INDIRECT to retrieve that data. So we're going to type =SUM(INDIRECT), and click on the name of the month. What that's going to do is return all of the values that named range refers to and sum them. And that's our total sales for October. You could also use this for slightly more sophisticated examples, like I would like to get the average price of the top three sales in October. So I'm going to use a combination of the AVERAGE and LARGE functions that we learned about earlier in the course. I'm also going to need to use INDIRECT so I can specify which sheet to get those from. And it's going to be whatever month we've selected. But I don't just want to get the largest or the second largest, I want all three. So now, I'm going to use my ROW function and I'm going to say, get me Rows 1 to 3. Close my brackets for my ROW function, close my LARGE, close my AVERAGE. And because I'm working with multiple rows, I'm going to need to press Ctrl+Shift+Enter. Another thing you can use a combination of INDIRECT and named ranges for is creating cascading list boxes. In other words, a list of items that changes depending on the option you choose from another list. For example, if we select Buckinghamshire, we want to get a list of just the districts within Buckinghamshire. And what Alex has done to make this easier, he has named the list of districts in Buckinghamshire Buckinghamshire. So we can use an INDIRECT function to return those values. Coming back to our Get Answers, click into C4, come to the Data tab and click Data Validation. We want a list, but we want that list source to come from an indirect reference to whatever they've selected in C3, click OK. And now you click the dropdown, there is the list of districts in Buckinghamshire. And, of course, if I change Buckinghamshire to, say, Bedford, I'll get the list of districts available in Bedford. Bear in mind, of course, that once you've selected the district, if you do change the county, the district will not automatically update, and there will be no indication that the two don't match. So it's still quite simplistic, but it can speed up these lookup processes. All right, so let's select a district in Buckinghamshire. We're going to go for Chiltern. And the last little problem we want to look at is getting the total sales for the selected month and selected district combination. Now obviously for this, SUMIFS will do the job. But we need to make the SUMIFS quite smart so we can work out whether to do August, September or October. We have not named the district column in each. But with our INDIRECT, we can also use either a worksheet name or, even more useful in this case, a table name or structured reference. And that's what we're going to do. So, we're going to type =SUMIFS. Our sum range, we have actually already used a named range for. So for that we can just use INDIRECT and refer to our month name and then comma. Our criteria ranges, however, we have not individually named. So we're going to need to use a combination of the table name and the structured reference district. So once again, we're going to put our INDIRECT. We're then going to type in the Sales_ because all our table names begin with that, and then an ampersand. We're going to join that to our month name. We're then going to need another ampersand, open our quotes, and open our square brackets, and the name of the column is District. Close the square brackets, close the double quotes, and close the brackets for your INDIRECT. And then we need to specify our criteria, which in this case is going to be our district. Close your brackets and Enter. And that is our total sales for October in Chiltern. So using a combination of INDIRECT, named ranges, and structured references, we've seen how we can create some really powerful formulas that will respond, not only to changes in user requests, but also to changes in the data. In the next video, we're going to have a look at the ADDRESS function, which is very often combined with INDIRECT. [SOUND]
