In this post, we’ll allow the user to select a table name from a data validation drop-down and our Excel formulas will operate on the values from the selected table. Thanks to Neelima for asking about this technique!
Before we get too far, let’s be clear about our objective. We have several data tables in several worksheets in our workbook. Each table stores data for one department. For example, here is the table for Department A, which has been named DeptA.
We would like the user to pick a table name from a drop-down list, and we’d like our formula to provide the total for the selected department, as shown below.
We’ll accomplish this with a little function known as INDIRECT. Let’s check it out now.
The INDIRECT function returns a valid Excel reference based on a text string. The syntax follows.
- ref_text is the text string of the desired Excel reference
- [a1] is an optional argument that allows us to specify an A1-style or RC-style reference
The INDIRECT function can create a valid Excel reference to a cell such as B10, to a range such as A1:B10, to a named reference, or to a table name such as DeptA. We have stored the data for our five departments in tables using the Insert > Table icon. We have named the tables DeptA, DeptB, DeptC, DeptD, and DeptE using the TableTools > Table Name ribbon field.
Our objective is to allow the user to enter the table name into a cell such as C6. Let’s say the user enters DeptA into cell C6. We can’t simply use the C6 reference in a function such as SUM(C6) to sum the values in the DeptA table. This is because the SUM function will return the sum of its argument C6, which in this case stores the text string value DeptA. The SUM function would thus return 0.
We need some way to tell the SUM function that we are not providing it with the sum range directly, but rather, we are providing it indirectly. That is, we are pointing it to the desired sum range. This is where the INDIRECT function comes to the rescue. It enables us to create a valid Excel reference from a text string, in this case, the text string of the table name stored in C6.
Rather than summing cell C6, we tell Excel to sum the values in the reference stored in C6 with the INDIRECT function, as follows.
The INDIRECT function converts the text string in C6, the table name DeptA, into a valid Excel reference. The SUM function sums the values stored in the DeptA table.
Now, the user can type the table name into cell C6 and the SUM function will return the total of all cells in the table. If we want to allow the user to select a table name from a list of tables, we can use the data validation feature to provide a drop-down.
On the input cell, we set up data validation (Data > Data Validation) to allow a list. We simply enter the table names using a comma-separated list, as shown below.
At this point, the user can select the desired department from the drop-down, and our formula updates accordingly, as shown below.
But, what if we want to do something more elaborate than just simply sum the values? For example, what if we wanted to do a lookup…can we do that? Yes! The INDIRECT function simply returns a valid Excel reference and can be used in other functions, including the VLOOKUP function.
If we wanted to allow the user to pick a department and we wanted our report to pull in the account values from the selected table, we could retrieve the values using a traditional lookup function such as VLOOKUP. The lookup range argument would simply use the INDIRECT function to provide the desired reference. For example, if we wanted to look up the value for the account name stored in B11 and the table name was stored in C6, we could use the following formula.
- B11 is the lookup value, the account stored in B11
- INDIRECT($C$6) is the lookup range, the table named in C6
- 2 will return the value from the 2nd column
- 0 means we want an exact matching value
The resulting workbook is shown below.
The INDIRECT function returns a valid Excel range. We can use it to convert a text string, such as the table name DeptA, into a valid table reference that can be used in formulas and functions such as SUM and VLOOKUP. Thanks again Neelima for asking about this approach!