KlingonCoder
New Member
- Joined
- Dec 20, 2011
- Messages
- 3
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-com
fficeffice" /><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
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
fficeffice" /><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
Code:
=INDIRECT($B$30)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
