Stop a formula from changing

S

Stuck for ideas

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
 
S

Stuck for ideas

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
 
F

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
 
S

Stuck for ideas

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'
 
S

Stuck for ideas

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
 
F

Frank Kabel

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