Dynamic Sheet Reference with INDIRECT – Excel & Google Sheets
In this Article
This tutorial will demonstrate how to use the INDIRECT Function to reference sheets dynamically.
Dynamic Sheet Reference using the INDIRECT Function
The INDIRECT Function allows you to reference a range with a string of text, making it very easy to change the cell reference. Its syntax is:
This example will reference cell C6 in worksheets Q1, Q2, Q3, and Q4:
In the above example, the Concatenate Operator (&) has been nested in the INDIRECT function to join the worksheet name and cell location.
INDIRECT – Volatile Function
Warning: The INDIRECT Function is volatile. This means it re-calculates every time the workbook re-calculates. If you rely on too many INDIRECT functions, it will negatively impact your workbook speed.
CHOOSE Function – Sheet Reference
The CHOOSE Function returns a value from a range or list of values based on a given position. Its syntax is:
For eg. CHOOSE(2,”Joseph”,”Rachel”,”John”) would return “Rachel”, as it’s the 2nd value listed after the index number.
The following example will reference total sales’ values for each quarter from worksheets Q1, Q2, Q3 and Q4.
In this example, column C specifies the position of the required value. Internally, the formula allots positions in the same order as selected, in other words, it’s the values’ position from the index number.
Note: If the number specified for the ‘index_num‘ argument is greater than the total number of values, Excel would return #VALUE! Error.
Dynamic Sheet Reference with INDIRECT in Google Sheets
These formulas work exactly the same in Google Sheets as in Excel.