How do I refer to cell A1 by using cell A2?

garyrobbins
garyrobbins used Ask the Experts™
on
Hello experts,
This should be easy for you guys who use Excel regularly.
I am developing a formula that refers to cell A1.  However, A1 can get deleted.  How can I refer to cell A1 from cell A2?  Ex. A2(-1 row)

Gary
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
In tables where formulas reference cells above or below the row in which the formula resides, cell references in those formulas get mangled (set to #REF or changed to other cells in other rows) when inserting and deleting rows in the table. This happens because Excel always tries to maintain cell references when making changes to the structure of a worksheet. But this very functionality can lead to undesired results in a table that is frequently manipulated by inserting and deleting rows.

The most obvious solution to this problem is to not reference specific rows above or below the row in which the formula resides. If this is not possible then use the OFFSET function. For example, if a formula in row 6 references a cell in row 5, column A, replace the reference A5 with OFFSET(A6,-1,0). When formulas are constructed this way Excel will always leave the cell references alone when inserting and deleting rows.

Kevin
You can do it by

=indirect("A1")

Commented:
You could also try this:

=INDIRECT("A1")

Because the A1 is not considered a "design-time" reference, it should always be pointing to cell A1, even if the current A1 cell is deleted.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Commented:
darn...ss beat me to it...
Maybe next time rs beats ss   ;-)
Top Expert 2008
Commented:
Note that with OFFSET, i.e. OFFSET(A2,-1,0), the reference will always be to the cell above because Excel will adjust the cell reference as rows are inserted and deleted. With INDIRECT, the reference will always be to cell A1 regardless of how many rows are inserted and deleted and where the formula ends up.

If you use OFFSET(A2,-1,0) then, if row 1 gets deleted, the formula becomes OFFSET(A1,-1,1) which results in an error but a correct formula. If you insert a row then the formula will again be adjusted and be correct with a reference to A1.

If you use INDIRECT("A1") and delete the first row you will get a circular reference warning.

Kevin

Author

Commented:
Thanks, Kevin.  OFFSET is what I needed.  I also appreciate your explantion.  Deleting intermediate rows is what I needed to deal with so that my formula was preserved.  In the future I will be more specific with an example to communicate my application.

Thanks, ss & rs for your suggestions but they do not work in my application.

Your guys are fast and good - I love EE!
Gary

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial