Copy & Paste Forumla - but reference cell is changing

[Guest]

I am trying to copy and paste a large section of one worksheet into another
but the reference cell in the formula keeps changing. How can I paste it so
that the reference cell remains the same regardless of where I paste it?

i.e. Cell B15 in Sheet 1 has the formula
‘=IF(model!AA25<>"",model!AA25,model!Z25)’. When I paste this into cell C8 in
Sheet 2, the formula changes to ‘=IF(model!AB18<>"",model!AB18,model!AA18)’
but I want it to still reference ‘model!AA25’…

 

[Guest]

hi
in xl help, look up absolute references vs. relative references.
Relative references varies depending on where you paste them.
aboslute references do not vary.
to make a relative reference absolute, put dollar signs in from fo the
column and row reference i.e. instend of A1, put $A$1.

regards
FSt1

 

[Guest]

Yeah, aware of absolute references, but the problem is that a lot of dragging
and forumla copying was needed in Sheet 1 and hence I never set the formulae
up as absolute...

 

[Guest]

hi,
then you have a problem. without absolute references, the formulas will
paste as relative references. no way around that.
sorry.
FSt1

 

[Pete_UK]

You could copy the formula from B15 of Sheet1 into B15 of Sheet2, then
move (cut/paste) it to C8. This also applies if you have a block of
cells relative to B15.

Alternatively, you could try to make the cell references in Sheet1
into absolute using Find & Replace - highlight the cells, then CTRL-H
and:

Find What: !AA
Replace with: !$AA$
Replace All

and again with !Z, but it depends how variable your formulae are as to
how easy this will be - you might have to apply it too many times to
make it worthwhile.

Hope this helps.

 

[Fov]

You couldcopytheformulafrom B15 of Sheet1 into B15 of Sheet2, then
move (cut/paste) it to C8. This also applies if you have a block of
cells relative to B15.

Alternatively, you could try to make the cell references in Sheet1
intoabsoluteusing Find & Replace - highlight the cells, then CTRL-H
and:

Find What: !AA
Replace with: !$AA$
Replace All

and again with !Z, but it depends how variable your formulae are as to
how easy this will be - you might have to apply it too many times to
make it worthwhile.

Hope this helps.

Pete...you're first suggestion is brilliant...so simple!