Deleted linked rows show REF error.

msmichaels

New Member
Joined
Oct 13, 2006
Messages
2
I am wanting to create a link on worksheet B to each cell in worksheet A that does not give an error #REF when the worksheet A cell or row is deleted. I even attempted to use the INDIRECT function, but I couldn't copy that formula down without having to re-type the cell reference.

What methods can I use to have a dynamically updated worksheet when referenced (or linked) cells or rows are deleted?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
  • This will link to Sheet1
  • It doesn't change the cell reference if you delete cells on Sheet1
  • You can drag\copy it
  • The cell address that the formula is in is the cell address on Sheet1 it links to e.g. the formula is in Sheet2 cell A1 then it will link to Sheet1!A1 (this could be changed)
Code:
=INDIRECT(ADDRESS(ROW(),COLUMN(),,,"Sheet1"))

The formula will return zero if the linked to reference on Sheet1 is blank.

This will return a blank if the linked reference is blank
Code:
=IF(INDIRECT(ADDRESS(ROW(),COLUMN(),,,"Sheet1"))="","",INDIRECT(ADDRESS(ROW(),COLUMN(),,,"Sheet1")))



  • This formula will also link to Sheet1
  • It doesn't change the cell reference if you delete cells on Sheet1 as long as you don't delete cell A1 in Sheet1
  • You can drag\copy it
  • The cell address that the formula is in is the cell address on Sheet1 it links to (this could be changed)
Code:
=OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1)
 

msmichaels

New Member
Joined
Oct 13, 2006
Messages
2
Thank you very much for the codes. They are successful.

After a little experimenting, I discovered that the () cell reference in the ROW() and COLUMN() part of the address formula will be the cell coordinate location in worksheet A, but will have the worksheet A tab text.

For example:
=INDIRECT(ADDRESS(ROW(B4),COLUMN(B4),,,"Sheet1"))

Note: The formula on worksheet B may be in a different cell than the worksheet A reference cell.

In my spreadsheet, for example, the formula on worksheet B is located in 'A8' but the reference cell on worksheet A is 'B4'. When I double click on the formula, the B4 cell is highlighted on worksheet B. This is important to remember and is what helps with the dynamic drag/copy feature. Very clever indeed.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top