< Go Back

Dynamic workbook reference

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.

Original Formula

=INDIRECT ("'["&workbook&"]"&sheet&"'!"&ref)

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.

Example

In this example, we have used the formula in F6 to build dynamic workbook reference. See the screenshot below.

=INDIRECT ("'["&C6&"]"&D6&"'!"&E6)

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.

‘[Example.xlsx]Sheet1’!A1

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.

=INDIRECT ("'["&C6&"]"&D6&"'!"&E6)

We have inserted the results into the INDIRECT function as a reference text.

=INDIRECT ("'[Example.xlsx] Sheet1'!A1")

Note

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.

 

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
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar