Quick excel query

[leosayer]

I have a formula in column A1 that references column A2.

How do I fix A1 so that it always looks at cell A2 even if I drag the contents out and move them to A3?

[Tripps]

leosayer said:

I have a formula in column A1 that references column A2.

How do I fix A1 so that it always looks at cell A2 even if I drag the contents out and move them to A3?

Always forget this myself, but IIRC you hit F4 when you've got the section of the cell reference you want to fix selected.

Or you can do it manually, by entering cell references in the form $A$3.

[leosayer]

I thought F4 as well but it doesn't work in my scenario because it follows the dragged cell around so for example in the formula in cell A1, $A$2 changes automatically to $A$3.

[TonyHetherington]

The dollar sign locks it, so you can do any iteration of $A$1 (so it will ALWAYS refer to that), or $A1 (so A remains constant but 1 can change) or A$1 (so A changes but 1 remains constant).

Push F4 to cycle through these options or just type the sign yourself in the formula bar.

Hope that helps
Tony

[TonyHetherington]

leosayer said:

I thought F4 as well but it doesn't work in my scenario because it follows the dragged cell around so for example in the formula in cell A1, $A$2 changes automatically to $A$3.

Ooops sorry only just read that bit. Urmmm, that's not quite right?! With $A$2 no matter what you do with the cell (i.e. extending it) that part of the formula will always refer back to A2...?!

[leosayer]

Try it Tony!

ie. drag the A2 cell contents elsewhere (or cut and paste) and the formula in A1 changes to new location (ie. where you dropped the contents of A2).