Persistent References to Data in other Sheets

[Guest]

Is it possible to have a cell in a worksheet reference a cell in another worksheet in the same workbook, and if the referenced cell is deleted, reference the new cell, rather than #REF

For examle, if I have something in Cell A1 on Sheet1, I can use =Sheet1!A1 on Sheet2 to display the contents of the cell. However, when I delete Row A on Sheet 1, so I can put new data into it, my reference changes to =Sheet1!#REF

How can I force the reference to stay =Sheet1!A1

 

[Paul]

Is it possible to have a cell in a worksheet reference a cell in another

worksheet in the same workbook, and if the referenced cell is deleted,
reference the new cell, rather than #REF?

For examle, if I have something in Cell A1 on Sheet1, I can use =Sheet1!A1

on Sheet2 to display the contents of the cell. However, when I delete Row A
on Sheet 1, so I can put new data into it, my reference changes to
=Sheet1!#REF!

How can I force the reference to stay =Sheet1!A1

Why do you delete the row rather than just clearing its contents? To clear
contents, select the row and press the Delete key on the keyboard.

To answer your question literally, you would use
=INDIRECT("Sheet1!A1")
But it's better to avoid having to use this - by clearing, as above.

 

[Guest]

I would just use the delete key, but this is going in an Excel file that will be manipulated by others. This issue has already come up once, and I can't imagine it getting better

Thanks for the tip

----- Paul wrote: ----

Why do you delete the row rather than just clearing its contents? To clea
contents, select the row and press the Delete key on the keyboard

To answer your question literally, you would us
=INDIRECT("Sheet1!A1"
But it's better to avoid having to use this - by clearing, as above

 

[Paul]

In that case, why not unlock the cells where users should be able to enter
data and then protecxt the worksheet? That way they can type in numbers and
use the delete key to clear them, but cannot delete the row.

I would just use the delete key, but this is going in an Excel file that

will be manipulated by others. This issue has already come up once, and I
can't imagine it getting better.

 

[Guest]

I remebered having troubles with that in the past, but I must have set some option or lock incorrectly, as it appears to be working perfectly now. Thanks

----- Paul wrote: ----

In that case, why not unlock the cells where users should be able to ente
data and then protecxt the worksheet? That way they can type in numbers an
use the delete key to clear them, but cannot delete the row