Copy & Paste Forumla - but reference cell is changing
 

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€¦

Copy & Paste Forumla - but reference cell is changing
 

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

"Andy" wrote:

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€¦

Copy & Paste Forumla - but reference cell is changing
 

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...

"FSt1" wrote:

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

"Andy" wrote:

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€¦

Copy & Paste Forumla - but reference cell is changing
 

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

"Andy" wrote:

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...

"FSt1" wrote:

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

"Andy" wrote:

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€¦

Copy & Paste Forumla - but reference cell is changing
 

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.

On Sep 27, 10:40 am, Andy wrote:
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...



"FSt1" wrote:
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

"Andy" wrote:

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'...- Hide quoted text -

- Show quoted text -

Copy & Paste Forumla - but reference cell is changing
 

On Sep 27, 5:23 am, Pete_UK wrote:
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.

On Sep 27, 10:40 am, Andy wrote:

Yeah, aware ofabsolutereferences, 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 asabsolute...

"FSt1" wrote:
hi
in xl help, look upabsolutereferences vs. relative references.
Relative references varies depending on where youpastethem.
aboslute references do not vary.
to make a relativereferenceabsolute, put dollar signs in from fo the
column and rowreferencei.e. instend of A1, put $A$1.

regards
FSt1

"Andy" wrote:

I am trying tocopyandpastea large section of one worksheet into another
but thereferencecell in theformulakeeps changing. How can Ipasteit so
that thereferencecell remains the same regardless of where Ipasteit?

i.e. Cell B15 in Sheet 1 has theformula
'=IF(model!AA25<"",model!AA25,model!Z25)'. When Ipastethis into cell C8 in
Sheet 2, theformulachanges to '=IF(model!AB18<"",model!AB18,model!AA18)'
but I want it to stillreference'model!AA25'...- Hide quoted text -

- Show quoted text -

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