Create and Use a Dynamic Worksheet Reference in Excel

When working in Excel, you might want to reference a worksheet whose name or other variables could name. In these cases, you would want to use a dynamic reference instead of a static one. 

Using a dynamic worksheet reference in Excel

The INDIRECT function in MS Excel can be used to formulate a formula that determines dynamic sheet name.

The general formula that is used to determine dynamic worksheet reference is as follows;

= INDIRECT(sheet_name&“A1”)

Figure 1: Dynamic worksheet reference:

The formula that is used in figure 1 is as follows:

=INDIRECT(B6&"!A1")

Explanation of formula

The INDIRECT function is used for this purpose which acts as a worksheet reference:

In figure 1, the sheet names are in column B, so it is referenced or joined with cell A1.

The formula becomes:

=INDIRECT (“sheet1!A1”)

The INDIRECT function identifies it as a valid function to cell A1 in sheet1 and returns with the value 100. A similar process is followed in all the cells.

Note

The data must not contain any type of spacing or punctuation otherwise it will give errors.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

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

Leave a Comment

avatar