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.

## Leave a Comment