Results 1 to 2 of 2
Thread: Name selection (2000+)
2005-02-09, 04:13 #1
- Join Date
- Jan 2001
- West Long Branch, New Jersey, USA
- Thanked 9 Times in 7 Posts
Name selection (2000+)
At work, I've become the resident guru on Excel - that's scary.
We have a timesheet template (xlt) that provides a worksheet for filling in your time. When you create a workbook (xls) for the past week, you are supposed to base it on the template. I made a bet with my boss that probably not more than 20% do it that way (as opposed to just copying last week's workbook).
At the top are cells for employee name and id number. What I'd like to do is as follows:
1. have some kind of list of names (doesn't matter at this point whether the list is a validation list, list box from the control toolbar, etc). This is easy.
2. not have any macros so people don't have to deal with the macro warning; while not an absolute rqmt, it is pretty close.
3. have the list reside in another file other than the xlt since we don't want the new workbooks (xls's) all having the list of names. Not that we'd worry about the file size since it would probably be a small increment (we only have about 25 employees).
4. prefer people not have to explicitly open another workbook (I think I saw an item suggesting you can have a list in a separate workbook but it has to be open). Of course, my xlt could open the workbook thru code, but that defeats #2.
Once a name is selected, I'd want to do a lookup from the same location to get id number.
So far from what I've seen, I couldn't find anything that satisfies all of the above. Did I just not see enough posts?
If tradeoffs are necessary, what are the solutions and what do I have to forego in the above rqmts lists?
2005-02-09, 08:22 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Name selection (2000+)
As far as I can see, requirements 2, 3 and 4 are contradictory. Although you can define a named range that refers to another workbook, it won't work if the workbook referred to is closed. You could, of course, create linked formulas in the template, but that would still mean that you have the list of names in the template.
Why not put the list of names in a hidden worksheet in the template? The worksheet will be copied to the workbooks, but the users won't see it. If you set the Visible property to 2 - xlSheetVeryHidden in the Visual Basic Editor, the users won't even be able to unhide it using Format | Sheet | Unhide...