<div>
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.<br />
<br />
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.<br />
<br />
Kevin
</div>


<div>
You can do it by<br />
<br />
=indirect(&quot;A1&quot;)
</div>


<div>
You could also try this:<br />
<br />
=INDIRECT(&quot;A1&quot;)<br />
<br />
Because the A1 is not considered a &quot;design-time&quot; reference, it should always be pointing to cell A1, even if the current A1 cell is deleted.<br />

</div>


<div>
Maybe next time rs beats ss &nbsp; ;-)
</div>


<div>
Thanks, Kevin. &nbsp;OFFSET is what I needed. &nbsp;I also appreciate your explantion. &nbsp;Deleting intermediate rows is what I needed to deal with so that my formula was preserved. &nbsp;In the future I will be more specific with an example to communicate my application.<br />
<br />
Thanks, ss &amp;&nbsp;rs for your suggestions but they do not work in my application.<br />
<br />
Your guys are fast and good - I love EE!<br />
Gary
</div>


<div>
<div class="content wysiwyg-content">
Hello experts,<br />
This should be easy for you guys who use Excel regularly.<br />
I am developing a formula that refers to cell A1. &nbsp;However, A1 can get deleted. &nbsp;How can I refer to cell A1 from cell A2? &nbsp;Ex. A2(-1 row)<br />
<br />
Gary
</div>
</div>


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

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

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.