Excel 2010 - using $ to fix a cell reference

[pendle666]

Hi

Column I in my spreadsheet has data values pasted from elsewhere.

Column K contains a formula :
[pre]
=$G$5-$I$5
=$G$6-$I$6

[/pre]

and so on. Because of the layout of the spreadsheet I have to move some of column I data down a few cells after pasting.

I though that using $ fixed a cell reference, however when moving data around in column I, the column K formulae is changing, one of them for example now says

[pre]
$G$12-$I$13
[/pre]

I can always use redo the formulae, but it would be good to know why this happens and if I can fix it so I can stop it happening in more complex workbooks.

thanks



thank you for helping

____________
Pendle

 

[apstef]

Try copying and pasting the data instead of moving them with the mouse via drag and drop. Then, delete the original data.

 

[pendle666]

Makes not difference, drag/drop or copy/paste, the formulae always change cell references despite the $

thank you for helping

____________
Pendle

 

[zelgar]

You do mention that you moved around data in Col I. If you have a formula based upon data from the cell and Move it or Cut/Paste, the formula referencing that cell will be modified.

 

[SkipVought]

If you MOVE the data in either G5 or I5, the formula in column K will change.

If you MOVE the formula in column K, the formula will not change given the absolute reference.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

Do dot insert cells/columns/rows too.

combo

 

[SkipVought]

Hey combo, got a stuffy nose today, eh?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

No Skip, it's rather a squint. I work now with two computers, conversion to office 2016 and testing all. A parallel computing.

combo

 

[SkipVought]

Ah, parallel universes? Thru the touch screen looking glass. Here we have the futuristic worlds of Alice and Dick Tracy all rolled into one.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pendle666]

okay thanks, a bit of jiggery pokery may be required

thank you for helping

____________
Pendle

 

[SkipVought]

Tell us what you're attempting to accomplish, rather than what you're trying to do.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}