Siam Hasan Khan
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.
How to Reference Cell in Another Sheet Dynamically in Excel: 5 Ways
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.
Read More: How to Make Different Types of Cell Reference in Excel
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
- 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: How to Reference Cell by Row and Column Number in Excel
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.
Read More: Mixed Cell Reference in Excel
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
- 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.
Similar Reading:
- How to Use Cell References in Excel Formula
- How to Display Text from Another Cell in Excel
- How to Reference Text in Another Cell in Excel
- How to Keep a Cell Fixed in Excel Formula
- How to Use Variable Row Number as Cell Reference in Excel
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
- 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.
Read More: How to Find and Replace Cell Reference in Excel Formula
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.
Read More: How to Use Cell Value as Worksheet Name in Formula Reference in Excel
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.
Download Practice Workbook
You can download the practice workbook from the link below.
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.
Related Articles
- How to Use OFFSET for Cell Reference in Excel
- How to Reference a Cell from a Different Worksheet in Excel
- Relative and Absolute Cell Address in the Spreadsheet
- Difference Between Absolute and Relative Reference in Excel
- How to Hard Code in Excel
- Absolute Reference in Excel
- Relative Cell Reference in Excel
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