Stop a formula from changing

[Guest]

I have a spreadsheet where I want to move the data from 1 cell to another.
But I don't want the formula to change.

For example cell 'b1' refers to 'a1' i want to move the data in cell 'a1' to
another cell but I want the formula in 'b1 to still reference 'a1' not the
data that was in 'a1'.

Any ideas

 

[Frank Kabel]

Hi
use
$A$1

--
Regards
Frank Kabel
Frankfurt, Germany

im Newsbeitrag
news:[email protected]...

 

[Guest]

That would work if i wanted to move 'b1', but, say the formula in 'b1' is
=$a$1 and I move the data in 'a1' to 'a3' the formula in 'b1' changes to
=$a$3. I don't want that to happen.
But thanks anyway

 

[Frank Kabel]

Hi
some questions:
- how do you 'move' your data
- please describe exactly:
-> what formula you're currently using (post this formula)
-> how you want to move the formula
-> you expected result

 

[Guest]

1. The formula in b1 is '=$a$1'
2. I drag or cut & paste the value in 'a1' to 'a3'
3. I want the formula in 'b1' to stay as '=$a$1' instead of changing to
'=$a$3'

 

[Frank Kabel]

Hi
=INDIRECT("A1")

 

[Guest]

Gotta go home will let you know tomorrow if it works.
Thanks

 

[Guest]

Tried it and it worked (too well!). Now all I need to do, is try and use the
same formula across the row but not be identical to the first one.
Thanks

 

[Frank Kabel]

Hi
if you have this formula in cell B1 you may try (not tested):
=OFFSET(B1,0,-1)
and copy this down

 

[Guest]

Thank you very much. Margaret in my office says you are a superstar.

 

[Frank Kabel]

Hi
thanks for the feedback and glad it works for you