# How to Reference Cell in Another Sheet Dynamically in Excel (5 Methods)

The two sample datasets show data on 2020 Sales shown in the B4:C14 cells which depict the Names of the sales reps and their Sales in USD respectively, and the 2021 Sales Dataset is shown in the following worksheet.

### Steps:

• Select cell C5.
• Enter the below formula.

`=Sales_Data_2022!C5`

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

• Use the Fill Handle Tool to copy the formula into the cells below.

• Repeat the process in the following column.

`=Sales_Data_2021!C5`

Here, “Sales_Data_2021!” is the name of the worksheet being referenced, while the C5 cell indicates the Sales value for Sam.

The resulting table should look like the image below.

### Method 2 – Utilizing INDIRECT Function

Steps:

• Select the C5 cell.
• Enter the below formula.

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

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

• Select cell D5 to enter the 2021 Sales Data with the below formula.

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

The results should look like the picture shown below.

### Method 3 – Combining Named Range and INDIRECT Function

Steps:

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

• Give the C5:C14 range of cells the name Sales_Data_2021 in the Sales_Data_2021 worksheet.

• 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. If you’re having trouble with the exact names, you can bring up the list of Named Ranges by pressing the F3 key on the keyboard.

• Select the C5:C14 cells and paste the following formula.

`=INDIRECT(F5)`

The F5 cell represents the Sales_Data_2022 Named Range.

• Repeat the procedure for the D5:D14 cells.

`=INDIRECT(F6)`

The F6 cells refer to the Sales_Data_2021 Named Range.

The results should look like the screenshot below.

### Steps:

• Go to the C5 cell and paste the following formula.

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

Here the “Sales_Data_2022” refers to the Named Range and the C5 cell indicates the Sales value for Sam.

• Select the D5 cell and enter the formula below.

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

Here the “Sales_Data_2021” refers to the Named Range and the C5 cell indicates the Sales value for Sam.

The results should appear as below.

### Method 5 – Applying VLOOKUP Function

Steps:

• Go to the C5 cell and paste the following formula.

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

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

• Repeat the process in the D5 cell to insert the data for the year 2021.

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

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

The results should appear as below.

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

Steps:

• Go to the C7 cell.
• Navigate to the Data tab.
• Click on the Data Validation drop-down. This opens the Data Validation window.

• In the Allow field, choose the List option.
• In the Source field, enter the Named Ranges as defined in method 3.

This will insert a Data Validation drop-down in the C7 cell as shown in the image below.

• Go to the C8 cell.
• Enter the below formula.

`=MAX(INDIRECT(C7))`

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.

• Calculate the Minimum Sales value in the C9 cell with the MIN function below.

`=MIN(INDIRECT(C7))`

`=AVERAGE(INDIRECT(C7))`

`=SUM(INDIRECT(C7))`

The results should look like the below image.

If you select Sales_Data_2021 from the drop-down then the results will appear from that sheet.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
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.

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!