Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Reference an identical cell on a different page using copy/paste?
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I convert text string into a cell reference | Excel Discussion (Misc queries) | |||
Problem with formulas changing cell reference | Excel Discussion (Misc queries) | |||
dynamic cell reference within a text string | Excel Worksheet Functions | |||
Return cell reference of lookup value | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |