name duplication via "Edit, Move or Copy Sheet"

Consider the following: I define the start and end of a range on one
worksheet (worksheet #1) by naming each and refer to the range on another
worksheet (worksheet #2) in a VLOOKUP function, with the notation
"name_start:name_end". I then copy both worksheets via "Edit, Move or Copy
Sheet" to a new workbook. In the new workbook, the VLOOKUP will use the
names referring to the starting workbook. In the new workbook, "Insert,
Name" will display the local names on "worksheet #1", but qualified by the
worksheet name on the right-hand side. On "worksheet #2" it will show the
names with the reference to the starting workbook. I'm looking for an easy
way to eliminate use of the names pointing to the starting workbook, and then
using the names pointing to the newer workbook--in the VLOOKUP function.
Simply deleting the names shown in "Insert, Name" on "worksheet #2" in the
newer workbook does not do the trick. I'm using MS Excel 2000.

One clarification: I should have said "Insert, Name, Define" where I wrote
"Insert, Name".
Also, I found an interim solution, which is to edit away the name of the
other file in the reference shown on "Insert, Name, Define" on worksheet #2
(new workbook). For completeness, one can delete the qualified names on
worksheet #1 (new workbook)--leaving in place the unqualified names.

"Alan" wrote:

Consider the following: I define the start and end of a range on one
worksheet (worksheet #1) by naming each and refer to the range on another
worksheet (worksheet #2) in a VLOOKUP function, with the notation
"name_start:name_end". I then copy both worksheets via "Edit, Move or Copy
Sheet" to a new workbook. In the new workbook, the VLOOKUP will use the
names referring to the starting workbook. In the new workbook, "Insert,
Name" will display the local names on "worksheet #1", but qualified by the
worksheet name on the right-hand side. On "worksheet #2" it will show the
names with the reference to the starting workbook. I'm looking for an easy
way to eliminate use of the names pointing to the starting workbook, and then
using the names pointing to the newer workbook--in the VLOOKUP function.
Simply deleting the names shown in "Insert, Name" on "worksheet #2" in the
newer workbook does not do the trick. I'm using MS Excel 2000.

Alan wrote:
I then copy both worksheets via "Edit, Move or Copy
Sheet" to a new workbook.

Do you copy them as a group? (click one tab and Shift+Click the other
before Edit / Move or Copy Sheet).
If you did, I doubt you would have this problem.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup