Question about moving Name Definitions

[dodsonmd]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I've noticed as a work on this file, an issue with name definitions has come up that's driving me crazy.

Here's the situation. I have sheet A with my roster, calcs, etc which works great with the name definitions for that sheet. I then want to duplicate that sheet, making sheet B, by option-click and drag within the same workbook. The sheet B is renamed by copying it, and the calcs don't work as their definitions refer to Sheet A.

If I open the the name definition dialog, select the name, select the entire range in the 'refers to' box, and then click the sheet B title tab, it changes the reference to the ranges in sheet B (which are exactly the same numerically as in sheet A, just in a different worksheet). Is there an easier to change the references in all the names without having to change each one individually? Maybe some way to make the original definitions (in sheet A) in some fashion that when I duplicate the sheet the names will travel with the sheet?

Any help appreciated.

 

[Ryan]

I have the exact same problem!

 

[dodsonmd]

With any luck, the Excel wizard down under, John McGhie, will see the post and have a suggestion.
I've had to continue to just change them manually each time. It works, but it's a pain.
PLease post any info or work arounds you find.
Thanks, /John

 

[John McGhie]

Gord Dibben posted on Saturday, January 12, 2008 5:24 PM

How are you doing the copy?

Right-click on the sheet tab and select Move or Copy then checkmark in
"Create a
copy" and select the workbook to copy to and OK

Range names go with the copy.


Gord Dibben MS Excel MVP

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
I've noticed as a work on this file, an issue with name definitions has come
up that's driving me crazy.

Here's the situation. I have sheet A with my roster, calcs, etc which works
great with the name definitions for that sheet. I then want to duplicate that
sheet, making sheet B, by option-click and drag within the same workbook. The
sheet B is renamed by copying it, and the calcs don't work as their
definitions refer to Sheet A.

If I open the the name definition dialog, select the name, select the entire
range in the 'refers to' box, and then click the sheet B title tab, it changes
the reference to the ranges in sheet B (which are exactly the same numerically
as in sheet A, just in a different worksheet). Is there an easier to change
the references in all the names without having to change each one
individually? Maybe some way to make the original definitions (in sheet A) in
some fashion that when I duplicate the sheet the names will travel with the
sheet?

Any help appreciated.

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]