Excel Formulas to get Dynamic reference Table name!!

With the INDIRECT function in combination with functions like SUM , we can do the dynamic reference of the table names to perform different calculations. This tutorial will walk through the steps to dynamically reference a set of data in other functions. Let’s see the simple formula used for dynamic reference table names in Excel. Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Generic Formula

• You need to use the below-given formula to get dynamic reference table names.

=[SUM](https://geekexcel.com/sum-function/)([INDIRECT](https://geekexcel.com/use-indirect-function-in-microsoft-excel-365-in-easy-ways/)(table&"[column]"))

Syntax Explanation

• COLUMN – This function is used to return column numbers for reference. Read more on the COLUMN Function.
• Parenthesis () – The main purpose of this symbol is to group the elements.
• SUM – The SUM function adds values to the cells.
• INDIRECT – The INDIRECT Function in Excel returns a valid reference from a given text string.

Practical Example

• Firstly, you need to create a sample data in Microsoft Excel.

Sample data

• Then, to calculate the total value for the region west, you have to use the below given formula in the formula bar.

=SUM(INDIRECT(K5&"[Amount]"))

Use the Formula

• Now, you will get the result for the region west as given below in the cell L5.

Result for region West

• After that, to calculate the total value for the region central, you have to use the below given formula in the formula bar.

=SUM(INDIRECT(K6&"[Amount]"))

Use the Formula

• You will get the result for the region central as given below in the cell L6.

Result for Central

• Now, to calculate the total value for the region east, you have to use the below given formula in the formula bar.

=SUM(INDIRECT(K7&"[Amount]"))

Use the Formula

• Finally, you will get the result for the region east as given below in the cell L7.

Result for East

Closing Words

Here, we have described the simple formulas for dynamic reference table names in Excel. I hope this article helps you. Feel free to ask back any questions and let us know how it goes. I will keep working with you until it’s resolved.

Thank you so much for visiting Geek Excel!! *If you want to learn more helpful formulas, check out Excel Formulas *!!

Related Articles:

• Excel Formulas to Generate Series of Date by Day ~ Easy Tricks!!
• Excel Formulas to Create the Series of Dates by Month ~ Simple Guide!!
• How to Change Chart Data as Per Selected Cell in Excel VBA?
• Excel Formulas to Create a Series of Dates by Year ~ Complete Guide!!
• Use Excel COLUMN Function in Office 365 – Simple methods!!