Help, change range names back to cell references?

[Guest]

Hi:

Created a worksheet and named many cells for formula purposes, but now I
want to duplicate the worksheet for other jobs. When I create the new
worksheet, the formulas retain the range names and refer back to the
originating worksheet not the current one. What's the easiest way to change
all the formula's to refer to the urrent worksheet. Deleting the name screws
up the formulas.

Thanks...

 

[Guest]

I think you just think it is referring to the same name. If you used a
regular copy|move sheet to make a copy of the sheet in the same workbook, it
also kept the named ranges on that sheet.

Try this with a new workbook, just to keep the confusion factor down:
Choose a cell on the sheet and use the Name Box to type in a name for that
cell. Call it MyTestCell. Put a value in it; 55. In the cell next to it
type the formula:
=MyTestCell
55 should show up in it.
Make a copy of the worksheet itself. It should look just like the first,
but change the 55 you typed into MyTestCell before to some other value, say
32. Now it and the cell next to it should show 32. Go look at the original
sheet, it should still say 55.


But if you've made reference to named ranges on other sheets, then you can
end up with a formula that looks like:
=S1B4_byDefineName+SecondSheet!S1B4_byDefineName
You can change the reference here to "SecondSheet" by selecting the cells on
the sheet and using Edit | Replace, click the [Options] button and make sure
that Formulas is chosen in the "Look in:" entry. Enter "SecondSheet" as the
"Find what:" entry, and the correct sheet name as the "Replace with:" entry.

 

[Guest]

Thanks for the reply:

What I have is 2 worksheets in 1 workbook, I made names for a bunch of
cells, when I was happy with the worksheet, I clicked in the top left corner
of the worksheet and did a copy/paste to "sheet2". Now when I click on the
dropdown list of range names and click on one, I will be thrown from "sheet2"
to "sheet1". The range name is stuck with "sheet1" which means I'll have to
change all the formulas back to cell references. Sorry for the lack of excel
verbage, newuser.

I think you just think it is referring to the same name. If you used a
regular copy|move sheet to make a copy of the sheet in the same workbook, it
also kept the named ranges on that sheet.

Try this with a new workbook, just to keep the confusion factor down:
Choose a cell on the sheet and use the Name Box to type in a name for that
cell. Call it MyTestCell. Put a value in it; 55. In the cell next to it
type the formula:
=MyTestCell
55 should show up in it.
Make a copy of the worksheet itself. It should look just like the first,
but change the 55 you typed into MyTestCell before to some other value, say
32. Now it and the cell next to it should show 32. Go look at the original
sheet, it should still say 55.


But if you've made reference to named ranges on other sheets, then you can
end up with a formula that looks like:
=S1B4_byDefineName+SecondSheet!S1B4_byDefineName
You can change the reference here to "SecondSheet" by selecting the cells on
the sheet and using Edit | Replace, click the [Options] button and make sure
that Formulas is chosen in the "Look in:" entry. Enter "SecondSheet" as the
"Find what:" entry, and the correct sheet name as the "Replace with:" entry.

Hi:

Created a worksheet and named many cells for formula purposes, but now I
want to duplicate the worksheet for other jobs. When I create the new
worksheet, the formulas retain the range names and refer back to the
originating worksheet not the current one. What's the easiest way to change
all the formula's to refer to the urrent worksheet. Deleting the name screws
up the formulas.

Thanks...

 

[Guest]

i suggest to file>save-as the workbook with only one original worksheet.

e.g. Workbook 1 :"original"<date>
Workbook 2 :"revision1" <date1>

place these 2 workbook under one folder....

Thanks for the reply:

What I have is 2 worksheets in 1 workbook, I made names for a bunch of
cells, when I was happy with the worksheet, I clicked in the top left corner
of the worksheet and did a copy/paste to "sheet2". Now when I click on the
dropdown list of range names and click on one, I will be thrown from "sheet2"
to "sheet1". The range name is stuck with "sheet1" which means I'll have to
change all the formulas back to cell references. Sorry for the lack of excel
verbage, newuser.

I think you just think it is referring to the same name. If you used a
regular copy|move sheet to make a copy of the sheet in the same workbook, it
also kept the named ranges on that sheet.

Try this with a new workbook, just to keep the confusion factor down:
Choose a cell on the sheet and use the Name Box to type in a name for that
cell. Call it MyTestCell. Put a value in it; 55. In the cell next to it
type the formula:
=MyTestCell
55 should show up in it.
Make a copy of the worksheet itself. It should look just like the first,
but change the 55 you typed into MyTestCell before to some other value, say
32. Now it and the cell next to it should show 32. Go look at the original
sheet, it should still say 55.


But if you've made reference to named ranges on other sheets, then you can
end up with a formula that looks like:
=S1B4_byDefineName+SecondSheet!S1B4_byDefineName
You can change the reference here to "SecondSheet" by selecting the cells on
the sheet and using Edit | Replace, click the [Options] button and make sure
that Formulas is chosen in the "Look in:" entry. Enter "SecondSheet" as the
"Find what:" entry, and the correct sheet name as the "Replace with:" entry.

Hi:

Created a worksheet and named many cells for formula purposes, but now I
want to duplicate the worksheet for other jobs. When I create the new
worksheet, the formulas retain the range names and refer back to the
originating worksheet not the current one. What's the easiest way to change
all the formula's to refer to the urrent worksheet. Deleting the name screws
up the formulas.

Thanks...

 

[Guest]

driller has probably made a good suggestion if you need to have those in
different workbooks.

Otherwise, just delete the second sheet, leaving you with just the original
sheet in the workbook. Then copy the entire original sheet as a sheet, not
as cells and formulas from one sheet to another. With the sheet as the
active one, there are (at least) two ways to do this:
From the Excel menu bar, choose
Edit | Move or Copy Sheet
indicate where in the book you would like to move it,
make sure that the [Create a Copy] option is selected, and click OK.

Second way: right-click on the sheet tab (tab with the sheet name on it) and
choose "Move or Copy..." from the list and continue as above.

Thanks for the reply:

What I have is 2 worksheets in 1 workbook, I made names for a bunch of
cells, when I was happy with the worksheet, I clicked in the top left corner
of the worksheet and did a copy/paste to "sheet2". Now when I click on the
dropdown list of range names and click on one, I will be thrown from "sheet2"
to "sheet1". The range name is stuck with "sheet1" which means I'll have to
change all the formulas back to cell references. Sorry for the lack of excel
verbage, newuser.

I think you just think it is referring to the same name. If you used a
regular copy|move sheet to make a copy of the sheet in the same workbook, it
also kept the named ranges on that sheet.

Try this with a new workbook, just to keep the confusion factor down:
Choose a cell on the sheet and use the Name Box to type in a name for that
cell. Call it MyTestCell. Put a value in it; 55. In the cell next to it
type the formula:
=MyTestCell
55 should show up in it.
Make a copy of the worksheet itself. It should look just like the first,
but change the 55 you typed into MyTestCell before to some other value, say
32. Now it and the cell next to it should show 32. Go look at the original
sheet, it should still say 55.


But if you've made reference to named ranges on other sheets, then you can
end up with a formula that looks like:
=S1B4_byDefineName+SecondSheet!S1B4_byDefineName
You can change the reference here to "SecondSheet" by selecting the cells on
the sheet and using Edit | Replace, click the [Options] button and make sure
that Formulas is chosen in the "Look in:" entry. Enter "SecondSheet" as the
"Find what:" entry, and the correct sheet name as the "Replace with:" entry.

Hi:

Created a worksheet and named many cells for formula purposes, but now I
want to duplicate the worksheet for other jobs. When I create the new
worksheet, the formulas retain the range names and refer back to the
originating worksheet not the current one. What's the easiest way to change
all the formula's to refer to the urrent worksheet. Deleting the name screws
up the formulas.

Thanks...

 

[Guest]

You can use the same range Names for two separate sheets. Excel interprates
the range names of duplicate sheets as belonging to the active sheet. Just
use Edit, Copy sheets (behing the original sheet).

I had originally meant to try a macro to convert range names to references
but it seems that it is not needed.

To test this select the copied sheet then use the Name Box to select the the
range. Excel will select the range on the active sheet, which had allready
been renamed.
Change some of the values on the copied sheet and the totals will change
while the original stays the same.

In the Names Definition box the ranges still refer to the original sheet but
in practice Excel overcomes this problem

Regards
Peter Atherton

driller has probably made a good suggestion if you need to have those in
different workbooks.

Otherwise, just delete the second sheet, leaving you with just the original
sheet in the workbook. Then copy the entire original sheet as a sheet, not
as cells and formulas from one sheet to another. With the sheet as the
active one, there are (at least) two ways to do this:
From the Excel menu bar, choose
Edit | Move or Copy Sheet
indicate where in the book you would like to move it,
make sure that the [Create a Copy] option is selected, and click OK.

Second way: right-click on the sheet tab (tab with the sheet name on it) and
choose "Move or Copy..." from the list and continue as above.

Thanks for the reply:

What I have is 2 worksheets in 1 workbook, I made names for a bunch of
cells, when I was happy with the worksheet, I clicked in the top left corner
of the worksheet and did a copy/paste to "sheet2". Now when I click on the
dropdown list of range names and click on one, I will be thrown from "sheet2"
to "sheet1". The range name is stuck with "sheet1" which means I'll have to
change all the formulas back to cell references. Sorry for the lack of excel
verbage, newuser.

I think you just think it is referring to the same name. If you used a
regular copy|move sheet to make a copy of the sheet in the same workbook, it
also kept the named ranges on that sheet.

Try this with a new workbook, just to keep the confusion factor down:
Choose a cell on the sheet and use the Name Box to type in a name for that
cell. Call it MyTestCell. Put a value in it; 55. In the cell next to it
type the formula:
=MyTestCell
55 should show up in it.
Make a copy of the worksheet itself. It should look just like the first,
but change the 55 you typed into MyTestCell before to some other value, say
32. Now it and the cell next to it should show 32. Go look at the original
sheet, it should still say 55.


But if you've made reference to named ranges on other sheets, then you can
end up with a formula that looks like:
=S1B4_byDefineName+SecondSheet!S1B4_byDefineName
You can change the reference here to "SecondSheet" by selecting the cells on
the sheet and using Edit | Replace, click the [Options] button and make sure
that Formulas is chosen in the "Look in:" entry. Enter "SecondSheet" as the
"Find what:" entry, and the correct sheet name as the "Replace with:" entry.


:

Hi:

Created a worksheet and named many cells for formula purposes, but now I
want to duplicate the worksheet for other jobs. When I create the new
worksheet, the formulas retain the range names and refer back to the
originating worksheet not the current one. What's the easiest way to change
all the formula's to refer to the urrent worksheet. Deleting the name screws
up the formulas.

Thanks...