How to reference cell in another sheet dynamically in Microsoft excel

Microsoft Excel is a spreadsheet software program developed by Microsoft Corporation. It is widely used for data analysis, data organization, and data presentation. Excel features a grid of cells, which can be filled with data, formulas, and functions to perform calculations and analyze data. It also supports graphs and charts, pivot tables, and macros to automate tasks. It is a powerful tool that is widely used in various industries, including finance, accounting, marketing, and project management. 

In this tutorial we will learn how to Reference a Cell in another sheet dynamically in Microsoft Excel. A dynamically referenced cell in Microsoft Excel is a cell reference that updates automatically when the data in the referred cells changes. This means that if the contents of the referenced cells are altered, the value in the cell that references them will also be updated. This is useful when creating formulas that need to reference multiple cells or when building templates or models where the data may change frequently.

Method 1 : Reference a Cell by link

Step 1 – Copy the Reference Cell 

  • Copy the Reference Cell containing data.
  • Right click on the cell, A pop-up menu will appear.
  • Click on copy.

Step 2 – Click on the cell which you want to refer to the Reference Cell

  • Click on the cell in another sheet which you want to refer to the copied cell.

Step 3 – Click on Paste List arrow

  • Click on the Paste List arrow in the Clipboard section of Home Tab.

Step 4 – Click on Paste Special

  • Click on the Paste Special option.

Step 5 – Click on Paste link

  • Click on the Paste link option in the bottom left of the Paste Special dialog box.

Step 6 – Check the Referenced Cell.

  • Go to the Reference cell which you copied earlier.
  • Change the text in the Reference cell.
  • Now go to the Referenced cell, the text change must be visible in the referenced cell.

Method 2 : Reference a Cell by Cell location

Step 1 – Select the cell to be referred from source sheet

  • Select the cell to be referred to in another sheet from the source sheet.

Step 2 – Place an Equals sign

  • Place an equals sign in the blank cell in the sheet where you want to refer the cell.

Step 3 – Enter the Location of the Reference Cell

  • To enter the location of reference we simply enter the name of the sheet in which the Reference cell is located i.e. In this case it’s Sheet2.
  • Place an Exclamation ( ! ) mark right after the name of the sheet.
  • Enter the address of the Reference cell with a ( $ ) sign before the column and the Row number i.e. $A$1.

Step 4 – Press the Enter Key.

  • Press the enter key and the cell will be referenced and the text in the Reference Cell will be visible in this cell.
  • If the text in the source cell will be changed the destination cell will automatically be updated.