How INDIRECT() works
The INDIRECT() function uses only one mandatory argument which either references to a cell or puts in a text. The syntax for this function is:
=INDIRECT(ref_text,[a1])To understand how it works, let’s take a simple example below:
Notice that Cell A6 contains the text “I6” while “Hello there…” is written in Cell I6. Go to cell B6 and write the formula below.
Cell B6 =INDIRECT(A6)This displays the text “Hello there…”. The INDIRECT() function uses the cell that you give it in the formula as the messenger to find out where to go. Instead of displaying the text found in cell A6, it uses A6 as the messenger which tells the function to go to cell I6.
You will also notice a second argument in the function that is displayed as:
- FALSE – R1C1
- TRUE – A1 style
This argument refers to the format of your cell referencing. The R1C1 style calls a cell by using its row number (R) followed by the column number (C) (e.g. R1C1, R1C2, R2C1, R2C2). The A1 style calls cells by column letter followed by its row number (e.g. A1, A2, B1, B2). If you do not specify a format, the function uses the A1 style by default.
Deleting the text in cell A6 causes the INDIRECT() function in cell B6 to result to an error indicated by #N/A!. This is because the messenger A6 has not given instructions as to where the function should go. Likewise, if you put the text “hello” in cell A6, the function also results to an error because it cannot find an address called “hello”.
Changing the address of a cell
Another way to refer to a cell is by using its name instead of the default A1 format. There are two ways to personalize the name of the cell.
OPTION 1
- Left click on the cell.
- Go to the Name Box at the upper left hand corner beside the formula box.
- Write the new name of the cell.
OPTION 2
- Go to the Formula tab.
- Click on Name Manager.
- Click on New.
- Specify a name and click on the cell it refers to.
After naming cell I6 as “greeting”, you can now refer to cell I6 using the address “greeting”. To try it out, use the INDIRECT() function in cell B7.
Cell B7 = INDIRECT(A7)
This displays the text “Hello there…” because cell I6 has been renamed to “greeting”. The function sees A7 as a text but first translates it to an address, “greeting”. Since the address “greeting” exists, it looks for the value that’s in that address.
In the same way, rename cell A1 to “hello” and write the function in cell B6 as follows:
Cell B6 =INDIRECT(A6)The function now displays the text found in A1, which is “INDIRECT Function”.
Be careful in writing your argument inside the INDIRECT() function. Putting the reference in quotation marks turns it into text.
Cell B6 =INDIRECT("A6")You will see that the function now displays the word “hello” instead of “INDIRECT Function”. In this case, the function translates the text to a cell reference and gives you what is in that cell.
What’s great about the INDIRECT() function is that it is dynamic. When the content of the destination cell is changed, the display shown by the function also changes accordingly. You can test this by changing “Hello there…” to “Hello there!”.
Using the INDIRECT() function to refer to data tables in different tabs
In some cases you will might need to pull data from different tabs. While you can do this by using nested IF() statements, the INDIRECT() function comes very handy.
Using the INDIRECT() function for the first time might be confusing. To start, you can create the formula assuming that the data is found in a single tab and see how you can use the INDIRECT() function from there.
In this example, there are two data tables, data_py and data_current. Both are found in separate tabs, namely Data_2016 and Data_2017. Each data table includes a revenue column for each division in various regions.
To locate the table name:
- Left click on the table.
- Under Table Tools > Design
- The table name is shown at the upper left corner.
Go back to your main tab. Use the SUMIFS() function to find the sum of revenues according to the divisions listed. The data tables have been formatted as Microsoft Excel tables so you will notice that instead of column letters, it uses the table headers.
Cell C15 =SUMIFS(data_py[Revenue],data_py[Division],Report1!B15)
Drag the formula in cell C15 down to C17. This will give you the sum of revenues for each division in year 2016.
The aim is to make the SUMIFS() function dynamic so that when you select the year 2016, it goes to the tab containing 2016 data, but selecting the 2017 sends it to the other tab. Go to the helper cells and write down the years that are specified in the dropdown option, 2016 and 2017. Specify the name of the data table corresponding to each year.
This is where we use a combination of the INDIRECT() and VLOOKUP() functions. But in order for this method to work, the headers of both tables need to be identical. To minimize the confusion of using multiple nested formulas, you can use helper cells which serve as intermediate values. This way, you can have the VLOOKUP() values here and reference to these cells instead.
Use the VLOOKUP() function to give you the name of the table containing the data for year selected in cell C14 and combine it with the header names. The & symbol concatenates both terms to give you the final reference.
Refer to the Revenue column by using the formula:
Cell K15 =VLOOKUP(C14,H15:J16,2,FALSE)&"[Revenue]"This results to data_py[Revenue]. Do the same for the Division.
Cell L15 =VLOOKUP(C14,H15:J16,2,FALSE)&"[Division]"
This gives you data_py[Division]. You will notice that if you change the year to 2017, the values in cells K15 and L15 automatically change to data_current[Revenue] and data_current[Division]. These can now be used as the reference text for your INDIRECT() function.
Rewrite the SUMIFS() formula in cell C15:
Cell C15 =SUMIFS(INDIRECT($K$15),INDIRECT($L$15),Report!B15)Since you want to be able to drag the formula in C15 down to C17 while retaining to the correct formulas, make sure the reference cells are fixed as $K$15 and $L$15.
Another option is to eliminate the use of the helper cells and do the VLOOKUP() directly inside the INDIRECT() formula which makes the final formula:
Cell C15 =SUMIFS(INDIRECT(VLOOKUP($C$14,$H$15:$J$16,2,FALSE)&"[Revenue]")),
INDIRECT(VLOOKUP($C$14,$H$15:$J$16,2,FALSE)&"[Division]")),Report!B15)This approach should give you the same result.
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
