Cascading Combo Box Problems

[KlingonCoder]

Hi there, I’m hoping someone may be able to help me solve a problem I’m having. I am quite familiar with Excel, but am just starting to use VBA to achieve the results that I usually use formulas for. I am currently trying to create a workbook in Excel 2003 for tracking personnel data for numerous employees. I have one worksheet that I use for various dynamic lists and named ranges i.e. Departments, Positions, Office Locations, Employee Status etc. The remaining worksheets are laid out so that they are each dedicated to a separate employee’s information.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
The situation that is plaguing me is that I am trying to create two cascading combo boxes. The first one is for the “Department” that an employee works for. This combo box is populated by a dynamic list and the second is for the “Position” within the selected Department. To make things even more of a pain, copies of this workbook will be used in different offices which are going to have some “Departments” and “Positions” that are unique to their location, therefore I can’t just create one list to rule them all, sorry bad pun. <o></o>
<o> </o>
For example, the following offices might have the following departments:<o></o>
Office 1: Management, Engineering, and Payroll <o></o>
Office 2: Drafting, <?xml:namespace prefix = st1 ns = "urn:schemas:contacts" /><st1:GivenName w:st="on">I.T.</st1:GivenName> and Security<o></o>
Office 3: Operations, Sales and Publications<o></o>
Head Office: all of the above plus another half dozen departments<o></o>
<o> </o>
Of course each department will have various positions within it, and the number of positions will vary from department to department.<o></o>
<o> </o>
I have no problem creating and naming a dynamic list for the departments, nor do I have a problem having the first combo box display those items. Where I am having difficulty is how to arrange my data and populate the second combo box. I originally had a list for the “Departments” and a second list for all “Positions”, which had two columns, one for the Position and the second for the parent Department. However, after reading numerous posts on dependant drop-down lists I tried rearranging my data so that each Department became a heading above a list of the Positions. This layout posed two problems:<o></o>
1. The first is that

=INDIRECT($B$30)

gave me an error when I tried to use it as the list source for the second dropdown list;<o></o>
2. But more importantly, I had no way of properly laying out the data since the Department list is going to vary from site to site, and I cannot trust that the users at each site will get things right<o></o>
<o> </o>
So after spending several hours trying to find a solution, I am here to plead for assistance. If anyone can suggest how to populate the list source for the second combo box I would be very grateful.<o></o>
<o> </o>
<o> </o>
Rob

 

[paddydive]

Hi Rob,

Can you post some examples of how your list look like.which you want to use in dropdown...

 

[KlingonCoder]

Hi Paddy,
Sorry not to have gotten back to you, things got nuts, other projects came up and to top it all off this problem is now moot as this project has been benched. Thanks for responding though.

Rob