Paste Name Questions

[cardan]

Hello,

I am working in an Excel Spreadsheet that is used as a template and
has about 30 page. I am revamping it and sometimes when I try to copy
a worksheet within the workbook, I get an error such as:

"A formula or sheet you want to move or copy contains the name '\P'
which already exists on the destination worksheet. Do you want to use
this version of the name?"
o To use the name as defined in the destination sheet, click Yes
o To rename the range referred to in the formula or worksheet, click
No, and enter a new name in the Name Conflict dialog box.

I have no clue what this means. I built the template and it is used
by a few other people but I am not sure how this got into my
workbook. I don't recall ever naming ranges.

When I copy and cell and hit F3, I get a very long list of Paste
Names. I am including them all below because I don't know if they are
unique to my spreadsheet or if someone can decipher what they mean.
They are separated by the comma.

\P, \PP, _1, _2, _3, _34536 , _346, _4, _5, _6, _7, A, asdf, asdfaf,
B, dfdgfh, FY4_, IFN, NvsElapsedTime, NvsEndTime, PED, PER, s,
SFD_HH_GL_CENTER, SFV_HH_GL_CENTER, W, Year

My questions are: What do they mean? Can I get rid of them? Should
I get rid of them? How do I get rid of them?

Any feedback is greatly appreciated. Thank you in advance.

 

[JLGWhiz]

If you are the only user of that workbook and do not have a use for any of
the names, then they could be deleted. However, if the workbook is a shared
workbook that you call up from an intranet server, you could be deleting
someone else's names that they use. In the standard dialog box for Define
Names, there is a Refers To: box at the bodttom that shows the sheet and
range for each name. Try clicking on some of the names and see what they
reference. Maybe that will help you decide what to do.

 

[Dave Peterson]

I would spend some time with those names to see if they're actually used
anymore.

I thought (mis-remembered???) that names that look like /P are the names of the
old Lotus macros -- this /P would be started by using alt-shift-p. But it's
been a long time...

Get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew Henson)
Name Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You'll be able to look at each name and see if it's used in this workbook. But
be careful. The names could be used in code somewhere (I don't think that the
NameManager looks at the code.)

And it doesn't check to see if there are any other workbooks (anywhere in the
world!) that rely on a name in your workbook.

But you'll be able to see if the name was an experiment that wasn't cleaned up
like asdf, asdfaf.

You'll be able to clean up those names (there's a Delete button on that
userform).

Some of the other names look like there was thought behind them: NvsElapsedTime
and even _3. You'll want to be careful when checking these.

In fact, you may want to make a backup of the original file before you start --
and even save as a new name before anything that looks dangerous.

 

[cardan]

I would spend some time with those names to see if they're actually used
anymore.  

I thought (mis-remembered???) that names that look like /P are the names of the
old Lotus macros -- this /P would be started by using alt-shift-p.  Butit's
been a long time...

Get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew Henson)
Name Manager:
NameManager.Zip fromhttp://www.oaltd.co.uk/mvp

You'll be able to look at each name and see if it's used in this workbook..  But
be careful.  The names could be used in code somewhere (I don't think that the
NameManager looks at the code.)  

And it doesn't check to see if there are any other workbooks (anywhere inthe
world!) that rely on a name in your workbook.

But you'll be able to see if the name was an experiment that wasn't cleaned up
like asdf, asdfaf.

You'll be able to clean up those names (there's a Delete button on that
userform).

Some of the other names look like there was thought behind them:  NvsElapsedTime
and even _3.  You'll want to be careful when checking these.

In fact, you may want to make a backup of the original file before you start --
and even save as a new name before anything that looks dangerous.

I just realized I did not mention that this workbook is set up to do a
mail merge into a Word document. (I guess I spent too much focus
typing out the Paste Names). Would a mail merge be an issue? I will
download that zip to see what I can find out and let you know. Thank
you for the feedback.

 

[Dave Peterson]

Is that mail merge a mechanized routine or is manual?

If it's manual, do you refer to any data by the name -- instead of an address
(or sheet and address???).

In fact, you'll have to check any macro/mechanized routine for the same thing.

 

[cardan]

Is that mail merge a mechanized routine or is manual?

If it's manual, do you refer to any data by the name -- instead of an address
(or sheet and address???).

In fact, you'll have to check any macro/mechanized routine for the same thing.

Sorry, I am not sure how to answer your question as to mechanized or
manual. I essentially have a worksheet dedicated for the mail merge.
The data on this tab is either manually entered or pulls from other
sheets. The data to be merged is in column D. Column B has the
corresponding names. Column A is numbers 1-255. I then have Vlookups
in the top 3 rows (A1:IU3) that find the corresponding number in
column A and returns the Name and Value to be mail merged. I have two
Tabs for mail merges (I have 2 different reports that pull from the
same spreadsheet).

I guess the mail merge into word would be manual. When we set it up
the mail merge, we have to manually click a button to update the mail
merge. The Excel workbook has to be closed when doing this.

I hope this helps shed some light on the issue. Thanks again

 

[cardan]

Sorry, I am not sure how to answer your question as to mechanized or
manual. I essentially have a worksheet dedicated for the mail merge.
The data on this tab is either manually entered or pulls from other
sheets.  The data to be merged is in column D. Column B has the
corresponding names. Column A is numbers 1-255.  I then have Vlookups
in the top 3 rows (A1:IU3) that find the corresponding number in
column A and returns the Name and Value to be mail merged.  I have two
Tabs for mail merges (I have 2 different reports that pull from the
same spreadsheet).

I guess the mail merge into word would be manual. When we set it up
the mail merge, we have to manually click a button to update the mail
merge.  The Excel workbook has to be closed when doing this.

I hope this helps shed some light on the issue.  Thanks again

So I just downloaded the Name manager (kind of above me) but it did
return the following "Refers to" for 272 lines

='\\corp-fs01\users\Documents and Settings\jdepaul\Desktop\NYC Barbizon
\Financial\Proforma (current)\[MEL NYC Barbizon Base Case with 98 & 99
& TT thru 0301 050701.xls]Pro Forma'!#REF!

WTF. I have no clue what this is- or who?

Any feedback?

 

[Dave Peterson]

There used to be a name that referred to a range in that file in that network
folder with the longgggg name.

But either the file was deleted (or renamed or moved) or the range was deleted
-- that's the #REF! indicator at the end of the formula.

Since it's an error, I would delete this name. I can't imagine the scenario
where it's worth keeping.



cardan wrote:

So I just downloaded the Name manager (kind of above me) but it did
return the following "Refers to" for 272 lines

='\\corp-fs01\users\Documents and Settings\jdepaul\Desktop\NYC Barbizon
\Financial\Proforma (current)\[MEL NYC Barbizon Base Case with 98 & 99
& TT thru 0301 050701.xls]Pro Forma'!#REF!

WTF. I have no clue what this is- or who?

Any feedback?

 

[Dave Peterson]

I meant the work you do in the mail merge in MSWord.

I'm not sure what button you click on to start that merge, though. If you're in
MSWord, do you click on Tools|Letters and Mailings (in MSWord 2003 menus -- or
its equivalent in MSWord2007)?

Or did someone create a macro that runs when you click a button?

If you have questions about the MSWord portion, then be very careful. You don't
want to break that button!

I'd try to find the person/department that set up the mailmerge and ask some
question.

If you can't do that, then try deleting the names that you think should be gone
-- but make sure there's a backup with those names still in it!



cardan wrote:

 

[cardan]

I meant the work you do in the mail merge in MSWord.

I'm not sure what button you click on to start that merge, though.  If you're in
MSWord, do you click on Tools|Letters and Mailings (in MSWord 2003 menus -- or
its equivalent in MSWord2007)?

Or did someone create a macro that runs when you click a button?

If you have questions about the MSWord portion, then be very careful.  You don't
want to break that button!

I'd try to find the person/department that set up the mailmerge and ask some
question.

If you can't do that, then try deleting thenamesthat you think should be gone
-- but make sure there's a backup with thosenamesstill in it!

cardan wrote:

<<snipped>>

Hi Dave, thank you for the response.

When we originally set up the mail merge, we use the Mail Merge Wizard
(Mailings->Start Mail Merge-> Step by Step Wizard) We run Office 2007.
So no macro.

I deleted all the paste names that showed the REF error at the end.
There were around 270. This template has never left the office so I
am very perplexed and alarmed how someone else had links to it.

Thanks again!

Dan

 

[Dave Peterson]

Maybe you didn't start with a brand new workbook.

Maybe you started with a workbook that was similar. Then started making
changes. So you'd inherit all the good stuff you wanted and all the not-so-good
stuff you didn't want.

It's happened before <vbg>.