ExcelBanter - Retain cell reference after deleting rows

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Retain cell reference after deleting rows (https://www.excelbanter.com/excel-worksheet-functions/210786-retain-cell-reference-after-deleting-rows.html)

Otto Moehrbach[_2_]

Retain cell reference after deleting rows

Excel XP & Win XP
An OP has asked me a question I can't answer. Simplified, the problem is
that she has a formula in Sheet2, say:
=Sheet1!A10
Someone then deletes rows 1:2 in Sheet1. Her formula changes to:
Sheet1!A8
This occurs regardless of whether she uses relative or absolute referencing
in her formula and I fully understand why.
The Problem: She wants her formula to stay =Sheet1!A10.
How can she do that? Thanks for your time. Otto

Gary''s Student

Retain cell reference after deleting rows

=INDIRECT("Sheet1!A10")

--
Gary''s Student - gsnu200814

David Biddulph[_2_]

Retain cell reference after deleting rows

=INDIRECT("Sheet1!A10")
--
David Biddulph

"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
An OP has asked me a question I can't answer. Simplified, the problem is
that she has a formula in Sheet2, say:
=Sheet1!A10
Someone then deletes rows 1:2 in Sheet1. Her formula changes to:
Sheet1!A8
This occurs regardless of whether she uses relative or absolute
referencing in her formula and I fully understand why.
The Problem: She wants her formula to stay =Sheet1!A10.
How can she do that? Thanks for your time. Otto

All times are GMT +1. The time now is 04:48 PM.