Deleted linked rows show REF error.

[msmichaels]

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?

 

[AlphaFrog]

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

=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

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

=OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1)

 

[msmichaels]

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.