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.

**Table of Contents**hide

## 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.

### Syntax

**=INDIRECT (Text_ref, [Cell])** which returns an authentic worksheet reference.

### Arguments

**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.

### Notes

- 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.

**
**

**Note:**If any of the cells contain no values. The formula gives an error result. To prevent this, you can use the below formula:

`=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.

## Conclusion

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.

## Further Readings

- How to Use ROW Function in Excel (With 8 Examples)
- How to Use OFFSET Function in Excel (3 Examples)
- How to Use ROWS Function in Excel (With 7 Easy Examples)
- How to use COLUMNS Function in Excel (3 Examples)
- How to Use COLUMN Function in Excel (4 Easy Examples)
- How to find text in an Excel range & return cell reference (3 ways)
- Offset(…) Function in Excel with Examples