How to Reference Cell in Another Sheet Dynamically in Excel

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.


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.

Dynamically Reference Cell in Another Excel Sheet

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

dataset for excel reference cell in another sheet dynamically

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.

Using Direct Cell Reference

Using Fill Handle

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

excel reference cell in another sheet dynamically

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

excel reference cell in another sheet dynamically with direct cell reference


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 the ref_text argument that returns the cell reference of the Sales value in the Sales_Data_2022 worksheet. The Ampersand (&) operator joins the sheet name and the cell reference.
    • Output → $2435

Utilizing INDIRECT Function

  • 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)))

Using Indirect and Address functions

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

excel reference cell in another sheet dynamically with INDIRECT and ADDRESS Functions

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.

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

Combining Named Range and INDIRECT Function

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

Named Range

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

Typing in Named Ranges

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

Using INDIRECT Function

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

excel reference cell in another sheet dynamically using Named Range


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 the lookup_value argument that refers to the Sales value for Sam. Following, Sales_Data_2022 represents the lookup_array argument (Named Range) from where the value referring to the C5 cell is matched. Lastly, 0 is the optional match_type argument which indicates the 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 the array argument (Named Range) representing the sales values in the C5:C14 cells. Next, 1 is the row_num argument which indicates the row location.
    • Output → $2435

Employing INDEX and MATCH Functions

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

Using INDEX and MATCH Functions

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

excel reference cell in another sheet dynamically by combining INDEX and MATCH functions


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 ( lookup_value argument) is mapped from the Sales_Data_2022 (table_array argument) Named Range. Next, 1 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
    • Output → $2435

Applying VLOOKUP Function

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

using VLOOKUP function

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

excel reference cell in another sheet dynamically with VLOOKUP function


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.

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

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.

Data Validation

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

Drop down list

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

Using MAX Function

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

=MIN(INDIRECT(C7))

Using MIN function

=AVERAGE(INDIRECT(C7))

Using AVERAGE Function

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

=SUM(INDIRECT(C7))

Using SUM Function

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

excel reference cell in another sheet dynamically based on cell value

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

excel reference cell in another sheet dynamically depending on cell value


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.

Practice Section


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.


Related Articles

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

2 Comments
  1. 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 xlsm file.
      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 an xlsm file even in this process. But, as your VBA is locked down, I am afraid you would not be able to work with xlsm file.
      So, I would say this would be quite impossible to solve your problem with VBA locked down.

      Regards,
      Tanjim Reza

Leave a reply

ExcelDemy
Logo