Tired of manually referencing cells in a different worksheet? Then I have some great news for you because in this article we’ll show you **how to dynamically reference cell in another Excel sheet** instead of typing them in manually. Furthermore, we’ll also explore how to reference a cell in another spreadsheet based on cell value.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from the link below.

## 5 Ways to Reference Cell in Another Sheet Dynamically in Excel

Excel offers multiple ways of applying dynamic cell referencing with the built-in functions and features, therefore, let us see each method individually and in detail.

Now, let us consider the **2020 Sales Dataset** shown in the **B4:C14** cells which depict the **Names **of the sales reps and their **Sales **in USD respectively.

In a similar manner, the **2021 Sales Dataset** is shown in the following worksheet.

Here, we have used *Microsoft Excel 365* version, you may use any other version according to your convenience.

__Method-1__: Using Direct Cell Reference

For our first method, we’ll begin with the simplest way to reference a cell from another worksheet. Thereupon, the process is illustrated in the steps shown below.

📌 ** Steps**:

- In the first place, go to the
**C5**cell >> type in the expression given below to pull in the corresponding sales data for 2022.

`=Sales_Data_2022!C5`

Here, the **“Sales_Data_2022!”** refers to the name of the worksheet which is *Sales_Data_2022* while the **C5 **cell indicates the *Sales* value for *Sam*.

- Then, use the
**Fill Handle Tool**to copy the formula into the cells below.

- Similarly, move to the
**D5**cell >> enter the following expression to bring in the corresponding sales data for 2021.

`=Sales_Data_2021!C5`

In this expression, the **“Sales_Data_2021!”** points to the worksheet name that is *Sales_Data_2021* and the **C5 **cell represents the *Sales* value for *Sam*.

Finally, after completing the above steps, the result should look like the image shown below.

__Method-2__: Utilizing INDIRECT Function

If you’re one of those people who enjoy using Excel functions, then the following method has you covered. Here, we’ll employ the **INDIRECT function** to store the cell reference and return its value to the current worksheet. Now, allow me to demonstrate the process in the steps below.

📌 ** Steps**:

- First and foremost, navigate to the
**C5**cell >> type in the expression given below to reference the cell corresponding to the sales data for 2022.

`=INDIRECT("Sales_Data_2022!"&ADDRESS(ROW(C5),COLUMN(C5)))`

Here, the **“Sales_Data_2022!”** indicates the name of the worksheet while the **C5 **cell indicates the *Sales* value for *Sam*.

**Formula Breakdown:**

**INDIRECT(“Sales_Data_2022!”&ADDRESS(ROW(C5),COLUMN(C5))) →**returns the reference specified by a text string. Here,**“Sales_Data_2022!”&ADDRESS(ROW(C5),COLUMN(C5))**is theargument that returns the cell reference of the*ref_text**Sales*value in the*Sales_Data_2022*worksheet. The**Ampersand (&)**operator joins the sheet name and the cell reference.**Output → $2435**

- Likewise, jump to the
**D5**cell for getting the*2021 Sales Data.*So, the formula will be like the following one.

`=INDIRECT("Sales_Data_2021!"&ADDRESS(ROW(C5),COLUMN(C5)))`

Lastly, the output should look like the picture shown below.

**Read More:** **Find Text in Excel Range and Return Cell Reference (3 Ways)**

__Method-3__: Combining Named Range and INDIRECT Function

For our third method, we’ll combine Excel’s **Named Range feature** with the **INDIRECT **function to dynamically refer to a cell in a different worksheet. Hence, let us understand and see the process in the following steps.

- Initially, proceed to the
*Sales_Data_2022*worksheet >> select the**C5:C14**cells >> enter a suitable name, in this case,*Sales_Data_2022*, in the**Name Box**.

- In a similar fashion, give a name for the
**C5:C14**range of cells in the*Sales_Data_2021*worksheet.

- Next, enter the
**Named Ranges**in the**F5**and**F6**cells as shown below.

📃 *Note: **Please make sure to type in the exact names, otherwise you may get an error. However, if you’re having trouble with the exact names you can bring up the list of Named Ranges by pressing the F3 key on your keyboard.*

- Then, select the
**C5:C14**cells and insert the expression given below.

`=INDIRECT(F5)`

Here, the **F5 **cell represents the *Sales_Data_2022 ***Named Range**.

- In a similar manner, repeat the procedure for the
**D5:D14**cells.

`=INDIRECT(F6)`

Here, the **F6** cells refer to the *Sales_Data_2021 ***Named Range**.

Eventually, the results should look like the screenshot given below.

__Method-4__: Employing INDEX and MATCH Functions

For those of you who want to learn about more techniques, you can combine the **INDEX** and **MATCH** functions to return the cell reference from another worksheet. So, just follow along.

📌 ** Steps**:

- At the very beginning, go to the
**C5**cell and copy and paste the following expression in the**Formula Bar**.

`=INDEX(Sales_Data_2022,MATCH(Sales_Data_2022!C5,Sales_Data_2022,0))`

In the above expression, the **“Sales_Data_2022”** refers to the **Named Range** and the **C5 **cell indicates the *Sales* value for *Sam*.

**Formula Breakdown:**

**MATCH(Sales_Data_2022!C5,Sales_Data_2022,0) →**returns the relative position of an item in an array matching the given value. Here,**Sales_Data_2022!C5**is theargument that refers to the*lookup_value**Sales*value for*Sam*. Following,**Sales_Data_2022**represents theargument (*lookup_array***Named Range**) from where the value referring to the**C5**cell is matched. Lastly,**0**is the optionalargument which indicates the*match_type***Exact match**criteria.**Output → 1**

**INDEX(Sales_Data_2022,MATCH(Sales_Data_2022!C5,Sales_Data_2022,0)) →**becomes**=INDEX(Sales_Data_2022,1) →**returns a value at the intersection of a row and column in a given range. In this expression, the**Sales_Data_2022**is theargument (*array***Named Range**) representing the sales values in the**C5:C14**cells. Next,**1**is theargument which indicates the row location.*row_num***Output → $2435**

- Following this, jump to the
**D5**cell >> enter the expression given below.

`=INDEX(Sales_Data_2021,MATCH(Sales_Data_2021!C5,Sales_Data_2021,0))`

In this formula, the **“Sales_Data_2021”** refers to the **Named Range,** in contrast, the **C5 **cell indicates the *Sales* value for *Sam*.

Consequently, the results should look like the image given below.

__Method-5__: Applying VLOOKUP Function

Another way on how to dynamically reference cell in another Excel sheet involves using the **VLOOKUP function** which returns a value according to the given row and column numbers. Now, it’s simple and easy, hence just follow the steps.

📌 ** Steps**:

- To begin with, navigate to the
**C5**cell >> insert the expression given below.

`=VLOOKUP(Sales_Data_2022!C5,Sales_Data_2022,1,FALSE)`

Here, the **“Sales_Data_2022!”** represents the worksheet name, the *Sales_Data_2022* points to the **Named Range**, and the **C5 **cell indicates the *Sales* value for *Sam*.

**Formula Breakdown:**

**VLOOKUP(Sales_Data_2022!C5,Sales_Data_2022,1,FALSE) →**looks for a value in the left-most column of a table, and then returns a value in the same row from a column you specify. Here,**Sales_Data_2022!C5**(argument) is mapped from the*lookup_value***Sales_Data_2022**(argument)*table_array***Named Range**. Next,**1**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***Exact match**of the lookup value.**Output → $2435**

- In turn, repeat the same process in the
**D5**cell to insert the data for the year 2021.

`=VLOOKUP(Sales_Data_2021!C5,Sales_Data_2021,1,FALSE)`

In this expression, the **“Sales_Data_2021!”** refers to the worksheet name, the *Sales_Data_2021* indicates the **Named Range**, and the **C5 **cell represents the *Sales* value for *Sam*.

Subsequently, your output should appear as the picture given below.

## How to Reference Cell in Another Sheet Based on Cell Value in Excel

Last but not the least, Excel has another nifty trick up its sleeve! In layman’s terms, you can pull data from another worksheet and perform multiple operations using Excel functions. Thus, let’s have a look at the procedures in simple steps.

📌 ** Steps**:

- First, move to the
**C7**cell >> navigate to the**Data**tab >> click on the**Data Validation**drop-down.

Now, this opens the **Data Validation** window.

- Next, in the
**Allow**field, choose the**List**option. - Then, for the
**Source**field, enter the**Named Ranges**as defined in the**previous method**.

Eventually, this inserts a **Data Validation** drop-down in the **C7** cell as shown in the image below.

- Second, jump to the
**C8**cell >> enter the formula given below to compute the

*Maximum Sales* value using the **MAX function**.

`=MAX(INDIRECT(C7))`

Here, the **INDIRECT function** stores and returns the values of the **Named Range **to the current worksheet while the **C7** cell refers to the *Sales_Data_2022*.

- Likewise, calculate the
*Minimum Sales*value in the**C9**cell with the**MIN function**.

`=MIN(INDIRECT(C7))`

- Third, obtain the
*Average Sales*by using the**AVERAGE function**as shown below.

`=AVERAGE(INDIRECT(C7))`

- Fourth, apply the
**SUM function**to calculate the*Total Sales*in USD.

`=SUM(INDIRECT(C7))`

Finally, the result should look like the screenshot shown below.

In addition, if you choose the *Sales_Data_2021* from the drop-down the results will be shown accordingly.

## Practice Section

Here, we’ve provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

## Conclusion

I hope this article helps you understand how to dynamically reference cell in another Excel sheet. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website **ExcelDemy**.

These methods depend on the sheet name. Is there a method where the sheet name is dynamic? I want to reference cells from a sheet to the left of the current sheet, but the sheet name will change as the formula is carried through the entire workbook. Is there a formula for that? My workplace has VBA locked down, so that’s not an option.

Hello, DJ!

Thank you for your query.

To analyze your problem, I would say it would be impossible to automate the sheet names completely without saving the file as an

xlsmfile.As far as I understood you want to create as many sheets as you want and then extract values from them in other sheets automatically whenever you want. In this regard, you want to address the sheets automatically without writing their names in the formulas.

Now, In this process, VBA codes are the most appropriate to use. Without VBA codes, you have another way to do this by using the

GET.WORKBOOK() function. But, you will need to save the file as anxlsmfile even in this process. But, as your VBA is locked down, I am afraid you would not be able to work withxlsmfile.So, I would say this would be quite impossible to solve your problem with VBA locked down.

Regards,

Tanjim Reza