Go Back

How to do Dynamic Reference of Table Name

With the INDIRECT function in combination with functions like SUM and AVERAGE, we can do dynamic reference of table name to perform different calculations. This tutorial will walk through the steps to dynamically reference a set of data in other functions.

Figure 1: Total Sales for the Three Cities

Setting up the Data

  • We will set up the data by inserting the dates and sales into Column A to Column F as shown in figure 2. We will also name them with the three cities as shown in the figure.

Figure 2: Data showing three tables named “Texas,” “California” and “Vegas.”

Dynamic Reference of Table Name with Range

We will input the cities into Cell A11 to Cell A13 and place the range of the sales in Cell B11 to Cell B13 as shown in figure 3.

Figure 3: Assigning selected range to each table name

Syntax

=function(INDIRECT(ref_text)

Formula

=SUM(INDIRECT(B11)

Using the SUM and INDIRECT function

We will sum the sales of the different cities using the formula above. We will insert this formula into Cell C11.

Figure 4: Using the SUM function for Dynamic Reference of Table Name

We will press enter to get the total sales.

Figure 5: Total Sales for Texas

We will use the drop-down to get the total sales for California and Vegas.

Figure 6: Total Sales for the Three Cities

Note

We can find the average sales by inputting the formula below:

=AVERAGE(INDIRECT(B11))

We can use other combinations with the dynamic reference to the table name.

Explanation

The INDIRECT function creates a dynamic reference to the table name “Texas” and returns the values of the Range for the table name, which, in this case, is “B5:B8”.  The SUM FUNCTION then totals the values in Cells B5 to B8.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I have a dynamic Table on the left of my sheet. On the right i have other figures linked to the camps of the dynamic table. I would like the figures on the right that are not in a dynamic table but link to the dynamic table contract and expand at the same time that the dynamic table. Hope this is clear
Solved by A. S. in 29 mins
I have a table with numeric customer references and also table that cross references the numeric reference to a customer name. I want to use vlookup to add the customer name to each numeric reference
Solved by C. H. in 18 mins
I have created a table using Insert/table. Works fine, now I want to reference the table in a 'vlookup' formula in another sheet of the same workbook. How do I use the table name in the 'table_array' argument of the formula ? Thanks.
Solved by D. J. in 28 mins

Leave a Comment

avatar