In this article, we will learn about dynamic worksheet reference using the INDIRECT function in Excel.
Dynamic worksheet reference suggests that using a value in one sheet from another.
The INDIRECT function is a cell reference function. It takes the value in the cell as address and returns the value in the address cell.
Let’s understand this function using it an example.
This is a dynamic cell reference example
Here As you can see B1 has the Formula indicating A1 cell and Text in A1 is “D”. So the function completes the full address to find the value and returns the value from the D1 cell which is 500 into B1 cell.
Now we will learn Dynamic worksheet reference
Here we have different values in A1 cell of Sheet1, Sheet2, Sheet3 & Sheet4.
Now I will get all these values in another sheet using INDIRECT function.
Use the formula:
Here & operator is used for joining the text to get a full address of the cell. A2 returns Sheet1 and get's join with the string “!A1” to get the full address of the worksheet with a cell reference.
As you can see all the values of different sheets in another sheet.
Hope you understood how to perform dynamic worksheet reference using Indirect function and referring cell in Excel. Explore more articles on Excel function here. Please feel free to state your query or feedback for the above article.