Dynamically refer to named ranges or sheets by using INDIRECT [Advanced]

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

If you have a workbook with different sets of data in separate sheets (and/or in separate named ranges) then you might want to reference these perhaps from a reporting worksheet. In the Topic Example Workbook, the Headcount data for each department (Sales, Technology etc.) are in separate sheets and we bring this data into the reporting worksheet. In cell C8 of the Reporting sheet for example we refer to =’Sales Headcount’!C5 to bring in the 2012 Sales department headcount figure. We could do a similar thing for all the other department worksheets.

But what if we instead wanted to have a single formula that is dynamic-rather than hard coding the sheet name “Sales Headcount” we would like to be able to select the sheet name from the drop-down list in cell B2 and the formula then flexes to pick up the data from the right sheet? This is what we can achieve using the INDIRECT function.

The INDIRECT function takes a reference to a cell/range on a worksheet and returns the value(s) which are in that cell or range. The syntax is =INDIRECT(ref_text) where ref_text represents the range we want to refer to, included in speech marks as a literal text string. For example on any given sheet if we type in =INDIRECT(“C8”) it will return the value in cell C8; if we type =SUM(INDIRECT(“C8:C9”)) it will return exactly the same as = SUM(C8:C9), i.e. the sum of values in cells C8:C9.

At first glance that might seem like a completely pointless function – why would you write =INDIRECT(“C8”) rather than just =C8? The answer is because it is the only way we can make the cell reference completely dynamic. If instead of linking to cell C8 we wanted to link to the value in column C and the row where we find the value “2012” in column B we could combine it with a MATCH function and write =INDIRECT(“C”&MATCH(“2012”,B:B,0)) which returns a reference to the cell in Column C and the row where it finds “2010” in Column B.

Again the above example seems like a convoluted way of doing a lookup – it would be much easier to use an INDEX/MATCH rather than an INDIRECT function. However, when we start moving from references within the sheet to a dynamic reference to any sheet then the INDIRECT function suddenly becomes very handy.

Let’s look again at the formula used in cell C8 on the Reporting sheet to refer to our Sales data : =’Sales Headcount’!C5. The sheet name has inverted commas either side and it is followed by an exclamation mark (indicating that is a reference to the sheet) and then is followed by the cell range we want to look at, in this case C5.

If we put this whole expression in speech marks and then encapsulate it in an INDIRECT function our formula becomes = INDIRECT(“‘Sales Headcount’!C5”) which correctly returns the value in cell C5 of the Sales Headcount sheet. (Note that the first part of the INDIRECT function is actually a speech mark followed by an inverted comma – “ ’.) Then we need to replace the text Sales Headcount with the text of whatever sheet name is listed in cell B2. The value in cell B2 is referred to by the named range SheetName and we can reference this in a middle of a formula by using the & character. So rather than = INDIRECT(“‘Sales Headcount’!C5”) our formula becomes = INDIRECT(” ‘ “ & SheetName & “ ’!C5”) . Note that this formula includes extra spaces so it is easier to read-when writing this there are no spaces in the formula! So looking at our formula, we start off with just putting an inverted comma in speech marks, followed by an ampersand sign and a reference to SheetName and another ampersand sign, and then we have the rest of our expression (an inverted comma, exclamation mark and cell reference) all in speech marks. With this formula when we select a different sheet name in cell B2 the formula automatically picks up the value in cell C5 of whatever sheet we have selected.

So we can use this formula to refer to cell C5 of our selected worksheet which will give us the 2012 headcount number for that department assuming our data worksheets are all in an identical format. However, when we drag down the formula = INDIRECT(” ‘ “ & SheetName & “ ’!C5”) to get the headcount figures for other years the formula doesn’t flex – the C5 doesn’t become C6,C7 etc. To get it to expand we need to use C&Row(5:5) instead of C5 so our INDIRECT formula becomes =INDIRECT(“‘”&SheetName&”‘!C”&ROW(5:5)).

Whilst undoubtedly useful, one of the biggest downfalls with the above approach is that it requires every spreadsheet to be in an identical format (much like with 3D formulas) and will break if the sheet name being referred to is subsequently changed. If instead we have our data in the same general format on different sheets but perhaps not in exactly the same location then it is still possible to dynamically refer to our datasets if we put them in separate named ranges or tables and use the INDIRECT formula to refer to these instead.

In the Topic Example Workbook, in each of our department worksheets we also have a [table showing the sales data for that department and have named these consistently e.g. tblSales, tblTechnology, tblFinance etc. The consistent naming of these tables is essential for this to work!

When we link to one of these tables from the Reporting worksheet, if we refer to a total or an entire column it does so using structured references – e.g tblSales[Projected Headcount] refers to the set of figures in the Projected Headcount column of our tblSales table. The formula =SUM(tblSales[Projected Headcount]) will give us the sum of these figures. Provided we have named our tables consistently and our column headings in each of the tables are the same we can combine this with INDIRECT replacing the text Sales with the department name that is chosen in cell B4 of the Reporting worksheet. So if our department name is in a named range called department an INDIRECT formula that reads =SUM(INDIRECT(“tbl”&Department&”[Projected Headcount]”)) will give us the sum of Projected Headcount figures in our tblSales table when department Sales is selected, the sum of figures in our tblTechnology table when department Technology is selected and so on.

This INDIRECT formula is much less messy and more flexible than the one where we refer to sheet names because it works regardless of where our tables are located on the source sheets, and moreover people are much less likely to change the names of the tables in our workbooks than they are sheet names! Also if the worksheet is protected users cannot change the table names so workbook protection isn’t necessary.

Note that we can use INDIRECT to refer to any named range – for example when on the Reporting worksheet the formula = INDIRECT(“Department”) will return the value in the cell(s) which has been assigned the named range Department, in this case cell B4. However because tables are so flexible (the reference tblSales[Projected Headcount] will always include all our sales headcount data even if rows are added/deleted from there ) I tend to use tables when using INDIRECT.

In the Topic Example workbook, build up the INDIRECT formula references in the yellow highlighted cells of the Reporting worksheet by:

• In cell B6 obtain the value in cell B2 by writing an INDIRECT formula that refers to the range “B2”.
• In cell E6 refer to the value in the cell referred to by named range Department by referencing this named range in an INDIRECT formula
• In cells C8:C18 obtain the sum of the sales department headcount figures for each year by linking to cells C5 to C15 on the ‘Sales Headcount’ tab (just using a normal link formula e.g. =’Sales Headcount’!C5)
• In cell D8 reference cell C5 on the Sales Headcount tab using an INDIRECT formula.
• Write the same formula in cell E8 but instead of hard-coding the sheet name ‘Sales Headcount’ refer to the sheet name selected in the drop-down box (cell B2, named range SheetName). Try changing the sheet name and ensure the value changes to pick up the value in cell C5 from the sheet selected.
• In cell F8 replace the reference to cell C5 with “C” & Row(5:5) so that the formula drags down. Drag down from F8 to F18 and ensure the correct figures are picked up for each year from the sheet selected.
• In cell G8 write an INDEX/MATCH formula to refer to look up the projected headcount for each year by referring to the various tables in the workbook – called tblSales, tblTechnology etc. Make this a dynamic reference to the table by referring to the department name selected in the drop-down in cell B4 (named range Department).

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

• INDIRECT is an example of a volatile function – i.e. all INDIRECT formulas are re-calculated every time anything is changed in the workbook-so excessive use of the INDIRECT function will slow the workbook down!

• When using INDIRECT to refer to a sheet name, the sheet name has to be consistent or the INDIRECT function will break! So either protect the workbook to prevent others from changing sheet names or use a formula such as =MID(CELL(“filename”),FIND(“]”,CELL(“filename”))+1,255) on every worksheet which returns the name of the worksheet, and then refer to this value instead.

• To write a formula that takes a given range from multiple sheets at one (e.g. sums cell C5 in multiple sheets) this can be achieved by using 3D formulas – just ensure you are aware of the caveats around using it!

YouTube Video – Excel INDIRECT Function Gets Reference from Text String