Named Cell Association with Excel Worksheet 1

[zerocul]

I have an Excel spreadsheet with several worksheets (i.e. Startup, Survey Form, Survey Log 04, etc.). The purpose of the spreadsheet is to track survey requests by individuals. When the individual creates a new survey request, the information is automatically stored on the Survey Log 04 worksheet. One column on the worksheet is titled Log Number. This log number is automatically generated and is sequential. Once the users information is input onto the log sheet, the cell containing the log number is automatically given a NAME through a VBA routine. The cell name is on the order of _04.1 where 04 is the year and 1 is the log number. My current Survey Log 04 worksheet has 217 entries in it. When I open Excel's "Define Name" dialog box, all the named cells on the Survey Log 04 sheet show up. All of the named cell entries show the name of the cell and out beside the cell name the Worksheet name that is associated with the named cell reference. This is true for all named cell references except the last five, numbers 211 to 217. Numbers 211 thru 217 only display the cell name. There is nothing out beside the cell name, i.e. worksheet name. How does this happen? More importantly, how do I get the worksheet name to appear beside log numbers 211 to 217. When I click on the drop down arrow of the NAME BOX in excel on any sheet other than Survey Log 04, only the 211 thru 217 cell names show up, however, when I activate the Survey Log 04 sheet and click on the NAME BOX, all the cell names appear including 211 thru 217. I would like to keep all the cell names related to the Log 04 sheet from appearing on other sheets. Is this possible and if so how? Thanks in advance.

 

[VBAjedi]

AFAIK named ranges are supposed to be visible to all sheets of the workbook they are created in, and you can't change this.

The issue of seven of your names behaving differently may be related to the number of Named ranges your workbook contains (although I haven't seen that one before).

Why is it so critical to create that many named ranges? Perhaps we can help you come up with an alternate approach to what you are trying to accomplish. . .

VBAjedi

 

[zerocul]

Thanks for the assistance. The named cells 211 thru 217 are visible to all sheets. The other 216 are only visible on the sheet that they were created on. It would be nice to be able to reproduce this affect for 211 to 217 because the spreadsheet will track all requests for the year 2004 on one worksheet and 2005 requests on another worksheet and so on. Any additional thoughts. Thanks again in advance.

 

[VBAjedi]

Again - what specifically are you doing with the named ranges? What are you trying to accomplish? Named ranges are great, but you're going to have to do something different if you're trying to isolate each sheet.

VBAjedi

 

[Gavona]

In Excel97 at least you get the following where there are several instances of the same rangename - each on a different worksheet. In all cases I am referring to Excel's "Define Name" dialogue:

a)The rangename is only shown once even though there are multiple instances of it.

b)If activated from either a sheet containing the first created version of the named range, or from a sheet not containing the named range then the worksheetsheet is not shown. (If you use PasteList then only the first instance of the named range is shown, and the worksheet is specified).

c)If activated from the sheet containing a second created instance of the name then the worksheet is displayed. In this case it is the active worksheet - i.e. the second created instance of the name. Again this is clear if you use Insert,Name,Paste,PasteList.

d)In all cases the 'refers to' area in the Insert,Name,Define dialogue shows the sheet name.

So, could it be that either
(i) names 211 to 217 were used for the first time on the sheet that was active when the Insert,Name,Define dialogue was entered?
or (ii) that these rangenames only exist on a single sheet whereas all the rest appear on several sheets?

You also said that "... When I click on the drop down arrow of the NAME BOX in excel on any sheet other than Survey Log 04, only the 211 thru 217 cell names show up?. "
I have not been able to replicate that - the only circumstances that I am aware of where named ranges do not show up in the name box are when they are dynamically defined.
May I suggest that you try Insert,Name,Paste,PasteList from both the Survey_Log_04 sheet and other sheets to see if that helps illuminate the issue?

Thanks,

Gavin

 

[zerocul]

Thanks for all your help. I was able to figure out how to associate the names with the sheet they reside on. If you copy the worksheet out of the original file and into a second excel file the named cell ranges only show in the name box when that specific worksheet is activated (in the second file). Thanks for all y'alls assistance.

 

[VBAjedi]

Gavin,
Thanks for that helpful information. You correctly identified/explained an aspect of the problem that I had written off as an anomoly. . .

Have a star!

VBAjedi