The Formula to Build a Dynamic Workbook Reference
It is a very difficult task to work on different workbooks at the same time, specifically, when you switch them frequently to check and verify different values in cells. In excel, The INDIRECT function gives you the instant solution to retrieve the values from other worksheets as well as workbooks. In this article, we’ll show you how to build a dynamic workbook/worksheet reference.
Explanation of the Formula
The INDIRECT function can be used in a formula to build a dynamic worksheet/workbook reference. Using this, you can fetch values from the cells that are in other worksheets or workbooks.
In this example, we have used the formula in F6 to build dynamic workbook reference. See the screenshot below.
Figure 1. Example of how to build dynamic workbook reference using formula
How This Formula Works
We have defined the workbook, worksheet, and range in three different cells. After this, we have changed this text into the reference using the INDIRECT function.
The external worksheet can be referenced using the following formula.
Figure 2. Example of Reference Worksheet used to fetch the Data
Make sure that the square brackets [ ] are around workbook name while single quotes (‘’) are put around the worksheet, and the range is followed by an exclamation mark (!).
By using text, you can create a reference like this. We have enjoined the values from the columns with the required brackets.
We have inserted the results into the INDIRECT function as a reference text.
=INDIRECT ("'[Example.xlsx] Sheet1'!A1")
Example.xlsx is the other worksheet that is being used along with the main worksheet as shown in the screenshot. The values are being fetched from Example.xlsx
In the last step, the INDIRECT function figures out the text and change it into a reference. Excel then returns the value from the given reference.
Important Note: You MUST open the referenced workbook and put a valid reference, or you will receive #REF error by the INDIRECT function.