In this article, we will discuss an important topic which is how “Excel reference cell in another sheet dynamically”.
Here we will refer to a spreadsheet cell in another spreadsheet. We will also do the referencing in other workbooks.
The referencing will be done by using the INDIRECT function. So, we need to know about this function first.
Download The Working File
Let’s introduce you to Excel INDIRECT function
The Indirect function in Excel returns an authentic reference from an assigned text string. It is categorized as the reference function in Excel.
=INDIRECT (Text_ref, [Cell]) which returns an authentic worksheet reference.
Text_ref– supplied text reference.
Cell– It is an optional argument. You can indicate it directly like, A1, B1, etc., or in the form of R1C1. It is used as Boolean input and by default, A1 is taken as True.
- For referring to another worksheet the worksheet must be open.
- The INDIRECT function evaluates the result in real-time
- The cell is taken as Boolean input and it is an optional argument. If it is taken as True then the cell reference style will be like this =A1
- If Cell is taken as False, then the worksheet will create a reference style of R1C1.
Some examples of INDIRECT Function (Get clear idea)
Let us look at some of the examples in the picture below. In the image below, column E is holding the formulas from column D.
How to Reference a Cell in Another Excel Worksheet Dynamically
Let`s create a workbook named Book1. In this workbook create 3 different worksheets named as Sheet1, Sheet2, Sheet3. In Sheet1 type 1-10 serially in the range A1:A10. In Sheet3 type 10-19 in the range A1:A10. We will do the worksheet referencing in Sheet2. So initially nothing is inserted in this sheet. After you provide the input in Sheet1 and Sheet3, do the following in Sheet2 to see the magic.
In cell A2 we write Sheet1 and cell B2 contains the cell name A9. In cell C2 we will get the reference result. The formula is written in cell C2 which is
=INDIRECT("'" & A2 & "'!" & B2)
For clarification, it is written in cell D2 by typing =FORMULATEXT(C2). The formula will output number 9 which is written in cell A9 of Sheet1.
The INDIRECT function first gets the reference from A2, where Sheet1 is written. Just before indicating A2, we used a set of double quotes. These quotes indicate the text string. As Sheet1 is written in cell A2 so, the function will take Sheet1 as the reference. “&” is used to combine the arguments together. “!” sign is used to separate sheets from a cell. So, by using “!” with “” we are actually referring Sheet1. Here a single quote is placed in between the double quotes for preventing errors (when the worksheet name has space in it). At the end of the Formula B2 is written which indicates the address of the cell.
In the same way,
=INDIRECT("'" & A7 & "'!" & B5)
which is written in cell C8 will return number 15 because we are referring to A7 which contains the name Sheet3 and in B5, A6 is written.
=IF(OR(A5="",B5=""),"",INDIRECT("'" & A5 & "'!" & B5))
Referencing to Separate Workbook Directly
Create a different workbook named Book2 and in there create a spreadsheet named Spreadsheet1. In Spreadsheet1 from A1 to A11 write down 20 to 30. Create a new spreadsheet name Sheet4 in Book1.
To insert data in Sheet4 from Spreadsheet1 of Book2 you can do the things below.
- Put an equal sign on cell A6 of Sheet4.
- Then go back to Spreadsheet1 of Book2 and select A6 where 25 is written and press enter. You will see the below result in Sheet4.
- As you can see in cell A6 of Sheet4 =[Book2.xlsx]Spreadsheet1!$A$6 is written. This happened because we did the referencing of Spreadsheet1 of Book2 in Sheet4 of Book1.
How you can reference a separate workbook dynamically
The previous process was for direct referencing. For the dynamic referencing follow the below processes.
- In A3, A4, and A5 of Sheet4 write Book2.xlsx, Spreadsheet1, and A6.
- In cell, A7 write
=INDIRECT("'[" & A3 & "]" & A4 & "'!" & A5)and press enter.
- You will see the value 25 as the A6 cell of Spreadsheet1 contains the value 25.
Here, the formula uses square brackets which are used for structured referencing. Here we refer to the workbook with the square brackets. After that, the referencing of worksheet and cell is the same as before.
Note: Keep that in mind, you need to type the workbook name with its extension (.xlsx, .xlsm, or so) if your windows system is showing the file names with their extensions. Here we type the workbook name Book2 like this Book2.xlsx because, on my Windows PC, my files are shown with their extensions.
It may look like that dynamic referencing of a spreadsheet is not that important. But, when you have a lot of worksheets and you need to gather the specific information of those sheets in a single worksheet you have to carry out the dynamic referencing for doing it easily.
Hope you find this article useful. Give us your feedback and stay tuned.