Results 1 to 2 of 2
Thread: Reversed Dropdown List? (2003 )
2007-09-13, 07:21 #1
- Join Date
- Jul 2006
- Thanked 0 Times in 0 Posts
Reversed Dropdown List? (2003 )
I'm revising a spreadsheet used to report billing charges to be entered into a rigid accounting program (SAP, in fact). Employees are assigned to one of some sixteen hundred accounting groups, all assigned different 5-digit numbers. These accounting groups are also assigned, and the accounting program requires their use, something called an Activity Type. MOST of the groups are deemed to be engaging in only one type of activity -- that is, engineers will be in a group whose activity type is ENGSVCS, estimators will be busy with ESTSVCS, managers MANAGE, and so on.
However, a few groups have managed to accrue more than one activity type. It's probably bureaucratic ego.
At the moment, an employee enters his accounting group number and its related activity type is retrieved with VLookup from a table. This is fine if there's only one entry for that group number, but if there's more than one, VLookup will only pick up the first entry. I am looking for a way to have an employee ENTER an accounting group number (with 1600 or so, a dropdown list doesn't seem practical) which will trigger a dropdown list of activity types -- a list of one in most cases, but a choice of more if there are more. The VLookup table I have is several columns across to allow for straight time, overtime, and double time, so any solution would have to allow for that.
I've seen dependent dropdown lists, but if feasible, it would almost certainly be better to link a dropdown list to a cell with entered data.
Suggestions most welcome,
(very glad to see everyone Back Up, too!)
2007-09-13, 11:44 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Reversed Dropdown List? (2003 )
You could use Advanced Filter to extract the activity types belonging to an Accounting Group to a separate range, and use that range as source for the dropdown list. See the attached example.
I used defined names (some of which are dynamic), and there is code in the worksheet module to automate the Advanced Filter.