Creating Names Baffling Problem

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!

Perhaps the issue is the hyphens in the name you give the range?

"Timmy Mac1" wrote:

>
> 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!
>
>
> --
> Timmy Mac1
> ------------------------------------------------------------------------
> Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
> View this thread: http://www.excelforum.com/showthread...hreadid=573060
>
>

Timmy Mac1 wrote:
> 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!

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

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


Timmy Mac1 wrote:
> 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!
>
>
> --
> Timmy Mac1
> ------------------------------------------------------------------------
> Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
> View this thread: http://www.excelforum.com/showthread...hreadid=573060

Thanks for the responses guys.

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

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

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