I need to copy a section of one sheet to another.

The section contains formulas referring to the same sheet.

When pasted into the second, the formulas should refer to the same cells
on the first sheet.

If I copy a formula with relative references e.g. =sum(A1:A5), it is
pasted as =sum(#ref!).

If I paste a formula with an absolute reference, e.g. it is pasted as
the same absolute reference e.g. =sum($A$1:$A$5) which refers to the
current sheet, not the originating sheet.

What I need it to be is =SUM(Sheet1!A1:A5)

How do I do this?

Your formula needs to be
=SUM(Sheet1!A1:Sheet1!A5)

Rgds
Alan

BMC wrote:

> I need to copy a section of one sheet to another.
>
> The section contains formulas referring to the same sheet.
>
> When pasted into the second, the formulas should refer to the same cells
> on the first sheet.
>
> If I copy a formula with relative references e.g. =sum(A1:A5), it is
> pasted as =sum(#ref!).
>
> If I paste a formula with an absolute reference, e.g. it is pasted as
> the same absolute reference e.g. =sum($A$1:$A$5) which refers to the
> current sheet, not the originating sheet.
>
> What I need it to be is =SUM(Sheet1!A1:A5)
>
> How do I do this?

Either seems to result in the same, but anyway, any ideas how to move a
load of formulas to a second sheet and refer to the original shhet, not
the respective cells in the new sheet?

Alan wrote:
> Your formula needs to be
> =SUM(Sheet1!A1:Sheet1!A5)
>
> Rgds
> Alan
>
> BMC wrote:
>
>> I need to copy a section of one sheet to another.
>>
>> The section contains formulas referring to the same sheet.
>>
>> When pasted into the second, the formulas should refer to the same cells
>> on the first sheet.
>>
>> If I copy a formula with relative references e.g. =sum(A1:A5), it is
>> pasted as =sum(#ref!).
>>
>> If I paste a formula with an absolute reference, e.g. it is pasted as
>> the same absolute reference e.g. =sum($A$1:$A$5) which refers to the
>> current sheet, not the originating sheet.
>>
>> What I need it to be is =SUM(Sheet1!A1:A5)
>>
>> How do I do this?
>

Oh I've been a silly old hector!

Cut and paste rather than copy and paste does it, which stands to
reasons as the point of the exercise was that I wanted to move the formulas!

BMC wrote:
> I need to copy a section of one sheet to another.
>
> The section contains formulas referring to the same sheet.
>
> When pasted into the second, the formulas should refer to the same cells
> on the first sheet.
>
> If I copy a formula with relative references e.g. =sum(A1:A5), it is
> pasted as =sum(#ref!).
>
> If I paste a formula with an absolute reference, e.g. it is pasted as
> the same absolute reference e.g. =sum($A$1:$A$5) which refers to the
> current sheet, not the originating sheet.
>
> What I need it to be is =SUM(Sheet1!A1:A5)
>
> How do I do this?

Why do they need to be "moved" ?? maybe youare creating stress for yourself?
maybe you can copy the current sheet - rename the tab and modify in or out
whatever you need to from the second copy of the sheet?
Seems a whole lot easier than copy/paste/rename/find and replace....

Just a thought.



"BMC" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Either seems to result in the same, but anyway, any ideas how to move a
> load of formulas to a second sheet and refer to the original shhet, not
> the respective cells in the new sheet?
>
> Alan wrote:
> > Your formula needs to be
> > =SUM(Sheet1!A1:Sheet1!A5)
> >
> > Rgds
> > Alan
> >
> > BMC wrote:
> >
> >> I need to copy a section of one sheet to another.
> >>
> >> The section contains formulas referring to the same sheet.
> >>
> >> When pasted into the second, the formulas should refer to the same
cells
> >> on the first sheet.
> >>
> >> If I copy a formula with relative references e.g. =sum(A1:A5), it is
> >> pasted as =sum(#ref!).
> >>
> >> If I paste a formula with an absolute reference, e.g. it is pasted as
> >> the same absolute reference e.g. =sum($A$1:$A$5) which refers to the
> >> current sheet, not the originating sheet.
> >>
> >> What I need it to be is =SUM(Sheet1!A1:A5)
> >>
> >> How do I do this?
> >