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;
Figure 1: Dynamic worksheet reference:
The formula that is used in figure 1 is as follows:
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:
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.
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.