< Go Back

Dynamic reference Table name

Using the Indirect function in combination with other Excel functions provides numerous possibilities in manipulating data.  Creating a dynamic reference to table name is one concrete example.

What is Dynamic Reference Table Name?

Dynamic reference to table name is created by using the Indirect function in Excel.  The Indirect function is used when you want to return a value based on a text string.  In this case, the text string is the table name and each table name has a pre-specified selected range.

Let us use the following data as shown in Figure 1.

Figure 1. Data showing three tables named “West”, “Central” and “East”

How to do Dynamic Reference Table Name?

Step 1: Assign selected range to each table name

Figure 2. Assign selected range to each table name using Name Manager in >Formula >Name Manager

Step 2: Insert the formula in Cell L5.

General Formula

=function(INDIRECT(table_name)

The Indirect function is used as the argument for a variety of Excel functions.  Let us try some examples using basic functions like SUM and AVERAGE.

Example 1: Sum-Indirect formula to total a dynamic range of values

=SUM(INDIRECT(table_name)

In Cell L5, enter the formula

=SUM(INDIRECT(K5)

where K5 contains the table name “West”.

Figure 3. Using the combination of Sum and Indirect function to get the total amount for each Region

The indirect function creates a dynamic reference to the table name “West” and returns the value of the range pre-selected for the table name “West”, which, in this case, is “C5:C7”.  The formula then totals the values in cells C5 to C7 which are $10,000, $5,500 and $12,000. The sum of these three values is $27,500.

Example 2: Average-Indirect formula to obtain the average of a dynamic range of values

=AVERAGE(INDIRECT(table_name)

In Cell L5, enter the formula

=AVERAGE(INDIRECT(K5)

where K5 contains the table name “West”.  In the same manner, the formula will return the average of the values from the dynamic reference to the table name using the Indirect function.

Figure 4. Using the combination of Average and Indirect function to get the average amount for each Region

Notes

The functions that can be used with Indirect function is not only limited to SUM and AVERAGE.  Other combinations can be used with the dynamic reference to the table name.  You can create multiple possibilities in manipulating data in tables with specified range values through the dynamic reference using INDIRECT function.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar