Modification of sheet references
 

In a single workbook, I have four sheets. For my purposes, I'll call them
"Sheet A", "Sheet B", "Sheet C" and Sheet D". "Sheet A" and "Sheet C" are
identical except for some labels and the value of a single cell. "Sheet B"
and "Sheet D" use data from "Sheet A" and "Sheet D" respectively to arrive
at their values. WIth the exception that "Sheet B" uses data from B3 in
"Sheet A" and "Sheet D" uses data from B3 in "Sheet C", "Sheet B" and "Sheet
D" are identical.

Now, suppose I've created "Sheet A", "Sheet B" and "Sheet C". I copy "Sheet
B" and need to change each of the sheet references in this new sheet so that
the references that point to cells in "Sheet A" now point to the
corresponding cells in "Sheet C".

In the instance I just finished, I explicitly edited the sheet references in
the top cell of each of 8 column in each of several tables and copied down to
fill each column. I suspect there must be an easier way. Any suggestions for
next time?

Modification of sheet references
 

Select columns.
Ctrl+H.
Find SheetA, Replace with SheetC (or whatever).
Make sure Options say to look in formulas.
Replace all.
I don't see any reason you can't record it as a macro, but I could be wrong.
In case you don't know how:
http://www.officearticles.com/excel/...soft_excel.htm
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************
"Jonathan Oz" wrote in message
...
In a single workbook, I have four sheets. For my purposes, I'll call them
"Sheet A", "Sheet B", "Sheet C" and Sheet D". "Sheet A" and "Sheet C" are
identical except for some labels and the value of a single cell. "Sheet B"
and "Sheet D" use data from "Sheet A" and "Sheet D" respectively to
arrive
at their values. WIth the exception that "Sheet B" uses data from B3 in
"Sheet A" and "Sheet D" uses data from B3 in "Sheet C", "Sheet B" and
"Sheet
D" are identical.

Now, suppose I've created "Sheet A", "Sheet B" and "Sheet C". I copy
"Sheet
B" and need to change each of the sheet references in this new sheet so
that
the references that point to cells in "Sheet A" now point to the
corresponding cells in "Sheet C".

In the instance I just finished, I explicitly edited the sheet references
in
the top cell of each of 8 column in each of several tables and copied down
to
fill each column. I suspect there must be an easier way. Any suggestions
for
next time?

Modification of sheet references
 

I tried doing a change using Edit Replace and yes, I had inside formulas
checked, but it did not alter the references. Does a sheet reference have to
be included in its entirety to be copied?

"Anne Troy" wrote:

Select columns.
Ctrl+H.
Find SheetA, Replace with SheetC (or whatever).
Make sure Options say to look in formulas.
Replace all.
I don't see any reason you can't record it as a macro, but I could be wrong.
In case you don't know how:
http://www.officearticles.com/excel/...soft_excel.htm
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************
"Jonathan Oz" wrote in message
...
In a single workbook, I have four sheets. For my purposes, I'll call them
"Sheet A", "Sheet B", "Sheet C" and Sheet D". "Sheet A" and "Sheet C" are
identical except for some labels and the value of a single cell. "Sheet B"
and "Sheet D" use data from "Sheet A" and "Sheet D" respectively to
arrive
at their values. WIth the exception that "Sheet B" uses data from B3 in
"Sheet A" and "Sheet D" uses data from B3 in "Sheet C", "Sheet B" and
"Sheet
D" are identical.

Now, suppose I've created "Sheet A", "Sheet B" and "Sheet C". I copy
"Sheet
B" and need to change each of the sheet references in this new sheet so
that
the references that point to cells in "Sheet A" now point to the
corresponding cells in "Sheet C".

In the instance I just finished, I explicitly edited the sheet references
in
the top cell of each of 8 column in each of several tables and copied down
to
fill each column. I suspect there must be an easier way. Any suggestions
for
next time?

Modification of sheet references
 

To my knowledge, no. I don't know why you'd have a problem. :(
Did anything occur? Or did it not Find?
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************
"Jonathan Oz" wrote in message
...
I tried doing a change using Edit Replace and yes, I had inside formulas
checked, but it did not alter the references. Does a sheet reference have
to
be included in its entirety to be copied?

"Anne Troy" wrote:

Select columns.
Ctrl+H.
Find SheetA, Replace with SheetC (or whatever).
Make sure Options say to look in formulas.
Replace all.
I don't see any reason you can't record it as a macro, but I could be
wrong.
In case you don't know how:
http://www.officearticles.com/excel/...soft_excel.htm
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************
"Jonathan Oz" wrote in message
...
In a single workbook, I have four sheets. For my purposes, I'll call
them
"Sheet A", "Sheet B", "Sheet C" and Sheet D". "Sheet A" and "Sheet C"
are
identical except for some labels and the value of a single cell. "Sheet
B"
and "Sheet D" use data from "Sheet A" and "Sheet D" respectively to
arrive
at their values. WIth the exception that "Sheet B" uses data from B3
in
"Sheet A" and "Sheet D" uses data from B3 in "Sheet C", "Sheet B" and
"Sheet
D" are identical.

Now, suppose I've created "Sheet A", "Sheet B" and "Sheet C". I copy
"Sheet
B" and need to change each of the sheet references in this new sheet so
that
the references that point to cells in "Sheet A" now point to the
corresponding cells in "Sheet C".

In the instance I just finished, I explicitly edited the sheet
references
in
the top cell of each of 8 column in each of several tables and copied
down
to
fill each column. I suspect there must be an easier way. Any
suggestions
for
next time?