Creating Names Baffling Problem

T

Timmy Mac1

Tearing my hair out here.

I have a spreadsheet operating with a number of worksheets. On one of
the worksheets I have highlighted a table of data and using INSERT -
NAME - CREATE option, used the labels on the top of the columns.

My problem is that when I go to use one of the names in a sumproduct on
another sheet, its as if it doesn't exist!

When I insert a new worksheet and use it to paste a list of the names
I'm using, it misses out the names pertinent to three or four of the
column names.

However, when I'm on the sheet itself, it shows the name I need for my
formula and lists it when I paste the names. It also shows it in the
name box when I highlight the range.

There appears to be nothing untoward about the names I'm using. For
example the one I need is -IssuingPolicyNo- with no spaces before or
after it.

Does anyone know why this is happening? It's driving me nuts!
 
F

Franz Verga

Timmy said:
Tearing my hair out here.

I have a spreadsheet operating with a number of worksheets. On one of
the worksheets I have highlighted a table of data and using INSERT -
NAME - CREATE option, used the labels on the top of the columns.

My problem is that when I go to use one of the names in a sumproduct
on another sheet, its as if it doesn't exist!

When I insert a new worksheet and use it to paste a list of the names
I'm using, it misses out the names pertinent to three or four of the
column names.

However, when I'm on the sheet itself, it shows the name I need for my
formula and lists it when I paste the names. It also shows it in the
name box when I highlight the range.

There appears to be nothing untoward about the names I'm using. For
example the one I need is -IssuingPolicyNo- with no spaces before or
after it.

Does anyone know why this is happening? It's driving me nuts!
Click to expand...

Hi Timmy,

I think your names are definied as local names instead of global ones, so
they are visible only in the sheet in which they are definied in. To verify
this, select the sheet with the names, then from menu Insert, Name, Define
you should see the names and on the right of the window you should read the
name of the sheet.

To use local names in a sheet different from the one it is defined, you must
use also the sheet name, so if you have a local name Pippo on a sheet named
Sheet1, to use this name on Sheet2 you have to use: Sheet1!Pippo


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
A

Andrew Taylor

I suspect you've created worksheet-level names rather than
workbook-level names. Select the worksheet where you
created the names, and bring up the Insert/Names/Define
dialog. Look for the "missing" name in the list: if I'm right
you should see it there with the sheet name to the right.

You can refer to such names in another sheet by prefixing
with the sheetname, e.g.

=Sheet1!MyName

but it's probably best to fix the problem at source by
deleting the rogue worksheet-level name and recreating
it as a workbook name. This should happen automatically:
I've never quite got to the bottom of how worksheet-level
names sometimes appear "accidentally" - it can happen
when you copy a sheet within a workbook but possibly at
other times too.

Andrew
 
T

Timmy Mac1

Thanks for the responses guys.

Although the actual name I was trying to use was not repeated elsewher
in the workbook, there were a couple of names that were.

Reading Franz and Andrew's responses it's now obvious to me that m
name was created on a local worksheet level. As for why it sometime
does this and sometimes doesn't I will hazard a guess that once on
name is duplicated and therefore can only be created locally, then al
names in that RANGE-NAME-CREATE sequence are treated as loca
references only.

Once again many thanks from a now not so baffled excel user :
 
Top