Reference an identical cell on a different page using copy/paste?:three worksheets

Are your Page 1 and Sheet 1 are one and the same?

At any rate, simply copy the formulas down, then select the pasted cells,
and do a Search & Replace, replacing Sheet1 with Sheet2

"Radar" wrote:

> I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> C1R1'. I'm having no luck. Can anybody help?

Duke,

I probably wasn't clear.

Page 1 and Page 2 are identical layouts with different values per cell.
Summary is a Rollup by Page #.

After I complete my 'Summary Row 1' formulas reference to Page 1 values
(across Summary Row 1), I want to simply copy 'Summary Row 1, paste it below
itself, however, I want it to now reference 'Page 2' values without having to
maually go into each cell and change the "worksheet' reference.

The bottom line is I may have 50 pages (or more) of data. I don't want to
edit worksheet references everytime I add a new line (refencing another page)
to the Summary page.



"Duke Carey" wrote:

> Are your Page 1 and Sheet 1 are one and the same?
>
> At any rate, simply copy the formulas down, then select the pasted cells,
> and do a Search & Replace, replacing Sheet1 with Sheet2
>
> "Radar" wrote:
>
> > I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> > R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> > 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> > C1R1'. I'm having no luck. Can anybody help?

You can use the indirect() function. Insert a new column A on your summary
sheet and put the Sheet name you want to reference for each row in the new
column A in that same row. Then your formula for Summary sheet row 5 would
be something like

=INDIRECT("'"&$a5&"'!a1")

and you'll be able to copy that down for all 50 sheets you want to
reference, so long as you put the sheet names in column A

You'll have to adjust the "a1" portion in each indirect() function, as this
is text and will not update as you copy across the columns to fill out each
row


"Radar" wrote:

> Duke,
>
> I probably wasn't clear.
>
> Page 1 and Page 2 are identical layouts with different values per cell.
> Summary is a Rollup by Page #.
>
> After I complete my 'Summary Row 1' formulas reference to Page 1 values
> (across Summary Row 1), I want to simply copy 'Summary Row 1, paste it below
> itself, however, I want it to now reference 'Page 2' values without having to
> maually go into each cell and change the "worksheet' reference.
>
> The bottom line is I may have 50 pages (or more) of data. I don't want to
> edit worksheet references everytime I add a new line (refencing another page)
> to the Summary page.
>
>
>
> "Duke Carey" wrote:
>
> > Are your Page 1 and Sheet 1 are one and the same?
> >
> > At any rate, simply copy the formulas down, then select the pasted cells,
> > and do a Search & Replace, replacing Sheet1 with Sheet2
> >
> > "Radar" wrote:
> >
> > > I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> > > R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> > > 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> > > C1R1'. I'm having no luck. Can anybody help?

Duke,

That did it.........just what I needed !

Thanx for sharing your expertise.

Radar

"Duke Carey" wrote:

> Are your Page 1 and Sheet 1 are one and the same?
>
> At any rate, simply copy the formulas down, then select the pasted cells,
> and do a Search & Replace, replacing Sheet1 with Sheet2
>
> "Radar" wrote:
>
> > I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> > R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> > 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> > C1R1'. I'm having no luck. Can anybody help?

Duke,

That did it.........just what I needed !

Thanx for sharing your expertise.

Radar

"Duke Carey" wrote:

> Are your Page 1 and Sheet 1 are one and the same?
>
> At any rate, simply copy the formulas down, then select the pasted cells,
> and do a Search & Replace, replacing Sheet1 with Sheet2
>
> "Radar" wrote:
>
> > I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> > R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> > 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> > C1R1'. I'm having no luck. Can anybody help?

You can use the indirect() function. Insert a new column A on your summary
sheet and put the Sheet name you want to reference for each row in the new
column A in that same row. Then your formula for Summary sheet row 5 would
be something like

=INDIRECT("'"&$a5&"'!a1")

and you'll be able to copy that down for all 50 sheets you want to
reference, so long as you put the sheet names in column A

You'll have to adjust the "a1" portion in each indirect() function, as this
is text and will not update as you copy across the columns to fill out each
row


"Radar" wrote:

> Duke,
>
> I probably wasn't clear.
>
> Page 1 and Page 2 are identical layouts with different values per cell.
> Summary is a Rollup by Page #.
>
> After I complete my 'Summary Row 1' formulas reference to Page 1 values
> (across Summary Row 1), I want to simply copy 'Summary Row 1, paste it below
> itself, however, I want it to now reference 'Page 2' values without having to
> maually go into each cell and change the "worksheet' reference.
>
> The bottom line is I may have 50 pages (or more) of data. I don't want to
> edit worksheet references everytime I add a new line (refencing another page)
> to the Summary page.
>
>
>
> "Duke Carey" wrote:
>
> > Are your Page 1 and Sheet 1 are one and the same?
> >
> > At any rate, simply copy the formulas down, then select the pasted cells,
> > and do a Search & Replace, replacing Sheet1 with Sheet2
> >
> > "Radar" wrote:
> >
> > > I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> > > R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> > > 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> > > C1R1'. I'm having no luck. Can anybody help?

Duke,

I probably wasn't clear.

Page 1 and Page 2 are identical layouts with different values per cell.
Summary is a Rollup by Page #.

After I complete my 'Summary Row 1' formulas reference to Page 1 values
(across Summary Row 1), I want to simply copy 'Summary Row 1, paste it below
itself, however, I want it to now reference 'Page 2' values without having to
maually go into each cell and change the "worksheet' reference.

The bottom line is I may have 50 pages (or more) of data. I don't want to
edit worksheet references everytime I add a new line (refencing another page)
to the Summary page.



"Duke Carey" wrote:

> Are your Page 1 and Sheet 1 are one and the same?
>
> At any rate, simply copy the formulas down, then select the pasted cells,
> and do a Search & Replace, replacing Sheet1 with Sheet2
>
> "Radar" wrote:
>
> > I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> > R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> > 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> > C1R1'. I'm having no luck. Can anybody help?

Are your Page 1 and Sheet 1 are one and the same?

At any rate, simply copy the formulas down, then select the pasted cells,
and do a Search & Replace, replacing Sheet1 with Sheet2

"Radar" wrote:

> I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> C1R1'. I'm having no luck. Can anybody help?

You can use the indirect() function. Insert a new column A on your summary
sheet and put the Sheet name you want to reference for each row in the new
column A in that same row. Then your formula for Summary sheet row 5 would
be something like

=INDIRECT("'"&$a5&"'!a1")

and you'll be able to copy that down for all 50 sheets you want to
reference, so long as you put the sheet names in column A

You'll have to adjust the "a1" portion in each indirect() function, as this
is text and will not update as you copy across the columns to fill out each
row


"Radar" wrote:

> Duke,
>
> I probably wasn't clear.
>
> Page 1 and Page 2 are identical layouts with different values per cell.
> Summary is a Rollup by Page #.
>
> After I complete my 'Summary Row 1' formulas reference to Page 1 values
> (across Summary Row 1), I want to simply copy 'Summary Row 1, paste it below
> itself, however, I want it to now reference 'Page 2' values without having to
> maually go into each cell and change the "worksheet' reference.
>
> The bottom line is I may have 50 pages (or more) of data. I don't want to
> edit worksheet references everytime I add a new line (refencing another page)
> to the Summary page.
>
>
>
> "Duke Carey" wrote:
>
> > Are your Page 1 and Sheet 1 are one and the same?
> >
> > At any rate, simply copy the formulas down, then select the pasted cells,
> > and do a Search & Replace, replacing Sheet1 with Sheet2
> >
> > "Radar" wrote:
> >
> > > I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> > > R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> > > 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> > > C1R1'. I'm having no luck. Can anybody help?

Duke,

That did it.........just what I needed !

Thanx for sharing your expertise.

Radar

"Duke Carey" wrote:

> Are your Page 1 and Sheet 1 are one and the same?
>
> At any rate, simply copy the formulas down, then select the pasted cells,
> and do a Search & Replace, replacing Sheet1 with Sheet2
>
> "Radar" wrote:
>
> > I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> > R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> > 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> > C1R1'. I'm having no luck. Can anybody help?

Duke,

I probably wasn't clear.

Page 1 and Page 2 are identical layouts with different values per cell.
Summary is a Rollup by Page #.

After I complete my 'Summary Row 1' formulas reference to Page 1 values
(across Summary Row 1), I want to simply copy 'Summary Row 1, paste it below
itself, however, I want it to now reference 'Page 2' values without having to
maually go into each cell and change the "worksheet' reference.

The bottom line is I may have 50 pages (or more) of data. I don't want to
edit worksheet references everytime I add a new line (refencing another page)
to the Summary page.



"Duke Carey" wrote:

> Are your Page 1 and Sheet 1 are one and the same?
>
> At any rate, simply copy the formulas down, then select the pasted cells,
> and do a Search & Replace, replacing Sheet1 with Sheet2
>
> "Radar" wrote:
>
> > I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> > R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> > 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> > C1R1'. I'm having no luck. Can anybody help?

Are your Page 1 and Sheet 1 are one and the same?

At any rate, simply copy the formulas down, then select the pasted cells,
and do a Search & Replace, replacing Sheet1 with Sheet2

"Radar" wrote:

> I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> C1R1'. I'm having no luck. Can anybody help?

Duke,

That did it.........just what I needed !

Thanx for sharing your expertise.

Radar

"Duke Carey" wrote:

> Are your Page 1 and Sheet 1 are one and the same?
>
> At any rate, simply copy the formulas down, then select the pasted cells,
> and do a Search & Replace, replacing Sheet1 with Sheet2
>
> "Radar" wrote:
>
> > I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> > R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> > 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> > C1R1'. I'm having no luck. Can anybody help?

You can use the indirect() function. Insert a new column A on your summary
sheet and put the Sheet name you want to reference for each row in the new
column A in that same row. Then your formula for Summary sheet row 5 would
be something like

=INDIRECT("'"&$a5&"'!a1")

and you'll be able to copy that down for all 50 sheets you want to
reference, so long as you put the sheet names in column A

You'll have to adjust the "a1" portion in each indirect() function, as this
is text and will not update as you copy across the columns to fill out each
row


"Radar" wrote:

> Duke,
>
> I probably wasn't clear.
>
> Page 1 and Page 2 are identical layouts with different values per cell.
> Summary is a Rollup by Page #.
>
> After I complete my 'Summary Row 1' formulas reference to Page 1 values
> (across Summary Row 1), I want to simply copy 'Summary Row 1, paste it below
> itself, however, I want it to now reference 'Page 2' values without having to
> maually go into each cell and change the "worksheet' reference.
>
> The bottom line is I may have 50 pages (or more) of data. I don't want to
> edit worksheet references everytime I add a new line (refencing another page)
> to the Summary page.
>
>
>
> "Duke Carey" wrote:
>
> > Are your Page 1 and Sheet 1 are one and the same?
> >
> > At any rate, simply copy the formulas down, then select the pasted cells,
> > and do a Search & Replace, replacing Sheet1 with Sheet2
> >
> > "Radar" wrote:
> >
> > > I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> > > R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> > > 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> > > C1R1'. I'm having no luck. Can anybody help?

I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
C1R1'. I'm having no luck. Can anybody help?

Duke,

I probably wasn't clear.

Page 1 and Page 2 are identical layouts with different values per cell.
Summary is a Rollup by Page #.

After I complete my 'Summary Row 1' formulas reference to Page 1 values
(across Summary Row 1), I want to simply copy 'Summary Row 1, paste it below
itself, however, I want it to now reference 'Page 2' values without having to
maually go into each cell and change the "worksheet' reference.

The bottom line is I may have 50 pages (or more) of data. I don't want to
edit worksheet references everytime I add a new line (refencing another page)
to the Summary page.



"Duke Carey" wrote:

> Are your Page 1 and Sheet 1 are one and the same?
>
> At any rate, simply copy the formulas down, then select the pasted cells,
> and do a Search & Replace, replacing Sheet1 with Sheet2
>
> "Radar" wrote:
>
> > I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> > R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> > 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> > C1R1'. I'm having no luck. Can anybody help?

Are your Page 1 and Sheet 1 are one and the same?

At any rate, simply copy the formulas down, then select the pasted cells,
and do a Search & Replace, replacing Sheet1 with Sheet2

"Radar" wrote:

> I have a three worksheets: 'Summary', 'Page 1' and 'Page 2'. On 'Summary
> R5C1', I have referenced 'Sheet 1, C1R1' . I now want to COPY the entire row
> 5 on 'Summary', paste it as R6 'Summary' but have it reference 'Sheet 2,
> C1R1'. I'm having no luck. Can anybody help?