The Magic of the INDIRECT Function of Excel - Five Examples of how to Use it | Alan Murray | Skillshare

The Magic of the INDIRECT Function of Excel - Five Examples of how to Use it

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
6 Lessons (20m)
    • 1. Introduction

      2:35
    • 2. Using INDIRECT with Named Ranges

      2:49
    • 3. Referencing other Sheets with INDIRECT

      4:27
    • 4. Return the Last Value from a Row

      4:24
    • 5. Using INDIRECT with VLOOKUP

      2:27
    • 6. Create Dependent Drop Down Lists

      3:14

About This Class

156f3096

In this class, we will uncover the magic of the INDIRECT function of Excel.

This lookup and reference function of Excel can be very useful when creating dynamic spreadsheets that can update when users change values or add new data.

In the class, we cover 5 useful examples of the INDIRECT function.

These include using it to dynamically reference worksheet names and named ranges from cells values. We will use it with the VLOOKUP function. To return the last value from a row and also for creating dependent drop down lists.

This function is a great one to get your teeth into and will make you a better Excel user.

All the files I use in the class are available to download, and there is a project to tackle at the end.

Enrol now. I'll see you in the class.

Transcripts

1. Introduction: Hello and welcome to this class where we look at the magic off the indirect function off Excel. So what are we going to cover in this class? Will, First of all, we need to answer the question off what is indirect Now. The indirect function is one of the look up and reference functions and excel on its job is to convert a text oring, so a text value that's written into a cell into a reference. So during this class will look at how we can use that. We've named Ranges, so we will use our value written into a cell as a dynamic reference to a named range on. We'll see real life cases where this could be useful in our formulas. Well, then, use it with the name of a sheet. So once again, a sale value but converting it into a reference to a worksheet. So we have some dynamic sheet referencing simply done by simply changing the value of a cell. Well, then, look at using it to return the last value from a row. So imagine you've got these columns at coming out, which may be for each month for the year, but next month, you is going to be one column or on if you're always trying to return the current month. It's always going to be an extra column, your range of values going to increase in in large over time, and you always going to have to find that last one. We'll show you how to do that in this class. Then we'll look at using indirect with the look up. I don't think we can do a class of that showing an example with the ever common and popular V Look up. So we will do that and I will finish off by showing it to create dependent, drop down lists so very popular to create drop down lists to make it easier and to validate data entry in Excel. But what if your list is huge? Well, we can look at breaking it up into smaller lists, so somebody makes a selection from one list on that determines what options were available in the next list on indirect will help us do that. So that sounds good. Let's get into it in role now on. I will see you inside the class 2. Using INDIRECT with Named Ranges: his first example. I've got the South people in column A and then I've got a south of free different regions in B, C and D set up to Liverpool Cardiff on. What I want to do is in cell G four. I want to produce a total for the region specified in four. Now Fred one who may be watching this and have never used the indirect function before. Its purpose is to convert Text Oring into a reference. So I want to refer to sell f four and use that name as a reference for that. Right now, something that's already been done here is I have created named Ranges. So if I select they'll be free to be 10 you can see in the top left corner that is called Southampton. Liverpool is called Liverpool and Cardiff is called Cardiff, and that's done simply by selecting the range off sales on by typing in a name in the top left corner. There you could look up more stuff about named Ranges and at the time, but they've already been named, and one good use of indirect function is to be able to reference known ranges using values off a cell. So in cell G four, I could write a simple some function, and it will ask me where the numbers all what wants doing? A. Some only some whatever's typed in F four company Cardiff. But that may change now. Cardiff is written as a value a text string on to convert that into, you know, a reference. I can just click on that sale when close bracket in tow. We won't know what I'm talking about. What I need to do in there is a need to put in direct opening bracket, then refer to that sale and then a closing bracket. You say the only mandatory question is the ref text. There's another one for a one which have got example of coming back shortly. Are you didn't know your own reference? Yes, I am at the moment closing bracket Press enter, and now I've got the total of Cardiff sows. But if I click and so therefore and type live a whole, I could get Liverpool sows or if a type Southampton I can get stuff haven't themselves. So it's all based on this dynamic, referencing up F or whatever F four says, because I've got these named Ranges that I was working assess the first example of indirect being able to use it with named Ranges. Let's move on to the next. 3. Referencing other Sheets with INDIRECT: Oh, enough que benefit of using indirect has been able to reference different shapes of a workbook. Sort of got here is I want to produce a total again and once again it's from the value of be free. But this time I'm not using named ranges on referencing different worksheets and exit the bottom. I have a sheet called India a shit. Could Sweden on a She could South Africa. And at the moment, you know, I want to be referenced in Sweden in its example, just having a look at these shapes. They've all got basic sounds. Information like the previous example. They're all the exact same ranges their values. Drancy For to see 11 off this example, they happen to be the same size ranges C four to C 11. The only thing that's different is wrong. Different sheets. So here we go again. It's another some function, and once again on guns need indirect Now, this time we're gonna have to create a bit more off the string SL's. We will need to come Katyn. Eight different elements of it together. So the ref text is going to start with the name of the sheet which is Sweden here? A k wherever is in be free. Then I'm gonna put in an 1% which is to concoct tonight to come join elements of the address. Together I'm gonna open a set off inverted commerce and in their type of exclamation mark, exclamation marks always follow the name for sheets in a reference. And then I'm going to type C for colon C 11 because they are the cells that contain those numbers of adding on all free sheets that the same size ranges I closed the inverted commas and I closed bracket for indirect and closed bracket for the some function I went up for sale on top. I've got the value for Sweden and if I change Sweden's in India or get the values for India , if I change values for South Africa was helps if attacked in the right cell. You guys could have told me there. It doesn't work, though, So the reason for this which some of you may have fought off as I approached, is that the shape South Africa has a spice in its name. And if I just demonstrate if I type in equal sign and reference that she could South Africa If I just click somewhere for a moment and press enter, that's what the reference looks like to see if the sheet name you have the exclamation mark have the sale references. But you also have these single quotations. These apostrophes around the sheet name Excel only does that if they're spaces in the name of the sheet. But that's what my full Mueller was missing. Now, even though I don't need those with India and Sweden, and I've kind of proven that by showing you that it's working, it would also do no harm if I put these in. So basically, that's what we're gonna have to do. Let me go back to the formula and before the Sheikh reference, I will put in my inverted commas a single quote, single, puffy, closed, inverted commerce that may look a little bit weird on screen that you now have gotten apostrophe surrounded by inverted commas. Doubling vertical Miss are there need the one percents to join the ship name on the end and then join the close in quote followed by the exclamation mark for about a range. So it just made sure that I got those single quotes or apostrophes around the sheet name. Now, on a precedent that it continues to work for India. It continues to work Sweden, but it now also works for South Africa, even though that sheet name contains spaces. So that is how we can use the indirect function to reference different sheets off a book, a very powerful example off needing indirect function. 4. Return the Last Value from a Row: Okay, now, in this third example, we're going to use in direct function. Fi are one C one reference. Now what I want to do here is get the last value off a table stress. Imagine I've got January February March and that's gonna expand into April May June. So it's an example of a table. It's always growing. Now, in its example, I want to get the last value and I made last column. That's what I mean. But this demonstration would work equally well for last row, if that's what you're trying to do. So what I want, hear it and sell f free. I want to get the value from so d 10 because that is the last column at the current month, if you will. But in time, you know next month is gonna be either month after BF month after Rejean, and so what? It may even reduce in time if people change this data for future years. So one of you is expandable. It's always changing. I want my four minutes to grab whatever the last cell in a row or column ease so in direct is going to do that. This time I will not need the some function or anything about that not total in anything. I'm simply grabbing a value from a cell. So I'm gonna put in indirect open bracket. And you see, the second question says, Are you one? But just Pork Omran? A moment To move into that, we've got two ways of referring to sell the A one style, which is your traditional way, you know, sell duty free. So D I so a one and in the all one c one style. So in a case of D 10 that is Row 10 Column four. That's what that is. Although we see column headers are A B. C. D. We can also reference than numerically, which really is what Excel does anyway. So it may be a little bit more difficult for us to relate to that when it comes to columns . Not with Rose. It doesn't anyway, but of columns. It's harder. No G seven. But there are some benefits to that, you know? So what I want to do here is for the first part, the ref text only open up a string. Inverted commas and type are substantial. 10 or so it would stand to row. Sorry, you haven't test on then. 10. So? So I'm off the road techniques while one all one C one reference in row 10. See what he stands for. Column that I'm gonna close inverted comma and and percent to join on. Now, I need to figure out what the column number is, and this is our puts for, but that's going to change. To find it. I'm going to bring in the county function which counts all non blank cells. Open a bracket up. I'm gonna ask it to count the values off Road 10 and I'm gonna close bracket for county and on then going to put in a coma. So I've now answered the ref text on back to the A One optional question. Is it interpreted as an all on C one store reference? Now I'm going to choose folks. This time I do want the r one c one style so I can double click on that where you can just type that in. Where is typically if you leave that blank clock, it did in the previous two examples. Then that would just use a traditional way One style closed bracket for indirect and went up. Press enter. I have that last value. But if a value was to appear later on but then have that value So it's always grabbing the last value from road him, which this example is the current month South's. So that's a really used, for example, very common question to get last row. Last column often excel Table, and it's also demonstration, also of R one. C one references another key benefit off the indirect function. 5. Using INDIRECT with VLOOKUP: How can I do examples off the indirect function without incorporating a V look up example? I don't think that would be fair now. So what I've got is those three tables again, which will hopefully look familiar. Seth Hampton, Newport, Cardiff, South People. This time I want to extract a particular South person cells from a particular region, and they have been displayed as separate tables. So in the top left on company, searching for Jessica's value in the table called Southampton on only Return South figure, which should come out. Eight. Free eight for Jessica. Now Vita, zoom in on this sheet for moment, and we're going to put V look up in Selby free so they look up. Value is Jessica be one. The table of Ray is going to be the region in there Now. I can't just click on B two, and I'm gonna demonstrate that I can't do that by doing it wrong. To begin with Comma column Index number is the second column of each table. Comma thous is an exact match. Now Exhale is gonna moan about that because I can't just refer to Texas in a cell. That is a reference liar need in direct. So if I put the indirect function in on then reference, be too, it will be quite happy for that to work. Now, once again, these three tables have Bean named. This is using a named range. If I click on my drop down list and you'll see a few others in here from this workbook at this moment that Liverpool, Southampton and Cardiff for in their virtues Cardiff it knows it's that table Liverpool is that Table and South Hampton is that table states using the value of a sale as a reference to one. Those Rangers by change have humps into Cardiff are now find Jessica's value from the card of table, the nine free free. That's an example of indirect being used with Evita Cup function to create a conditional table array. 6. Create Dependent Drop Down Lists: que the fifth and final example off indirect. This example uses indirect to create dependent drop down lists. This is one of my most popular videos on my YouTube channel and dog into corporate this as a another example of indirect in this video. So what I've got here is a list of countries and then vin each country. I have different regions. Let's imagine that's one of my shops. One of my stores, What I want to do in cell F two of the spreadsheet is have a drop down is to choose a country Canada, Germany or UK. And when I do that, a sorting office I've got here in sl g two i over drop down list off the regions, the offices in that country, so much Germany. I should only have released with those four. But you UK It should be a list of those five. It sets etcetera so that dropped down. This is dependent on the previous one. Now, once again, these ranges are named. If our highlight those free sales, it's called countries Hollows to its Canada Hollows. Four. Germany Hollows five its UK. They have been named in advance name ranges so F two data validation data tab data validation from my allow option. I'm going to choose list and for the source off the list I will type equals countries, which is the named range for Sells A to a four. So Canada, Germany, UK. When I click OK, I'll get a doctor honest of Canada, Germany, UK cause Daddy's the range core countries. What I need now is when somebody chooses the U. K. For example, I need these five offices appearing in this drop down ist so back to data validation for cell G two. Choose in a list and from the source because but I can't just say equals f to wherever F two says I have a named range called U K. I have named range called Germany, so I want to convert whatever string is in that cell to a reference off the same name. So in direct opening bracket F two, because that's to sell that's CO UK urine in it. Right now. That's the other drop down. It's it's dependent on close bracket Click. OK, don't worry if you get a warning message at that point in your examples. If this still is blank in the moment, I would've been warned about possible error. But if I got dropped down this now iconic five places if it changed the UK to Canada, I only have a job. Dentist of the two. I never changed to Germany. I only have a drop dentist ought. Therefore, Arkan Select stood got from the list, for example. And I have myself a second dependent drop down list thanks to the indirect function.