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.
Leave a Comment