Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! -- 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating Names Baffling Problem
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating Names Baffling Problem
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating Names Baffling Problem
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating Names Baffling Problem
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 :) -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem - When couples have different last names | Excel Discussion (Misc queries) | |||
Creating names | Excel Discussion (Misc queries) | |||
creating spreadsheet with colunms for names adresses & zipcodes | Excel Discussion (Misc queries) | |||
Creating "factorial" result from three lists... | Excel Discussion (Misc queries) | |||
creating names for data shown on a scatter plot | Charts and Charting in Excel |