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:
This approach should give you the same result.