Drop down boxes and Named ranges

[Harry_Potter]

Hi,

I'm new here and staring off with a hard one, I've had a look but found nothing to help me with this one yet.

I have a list of departments and a second list of jobs within each department.

As below.

dept Title
Dept1 sales mr
Dept1 sales ast
Dept1 sales admin
Dept1 sales team leader
Dept1 sales
Dept2 direct accouts
Dept2 direct accouts
Dept2 store accounts
Dept2 supplier accounts
Dept2 general accounts

I then have a cell with the value Dept1 in and have a dropdown box that uses =indirect(Dept1), (where dept1 is a referance to a cell) to show only the 5 options for jobs that people in dept1 can have. This then picks up all the jobs from the named range dept1 and works fine.

What I need to do is import a list of departments and jobs on a regular basis, it is a long list and I would to have the named ranged defined by the dept column, so it can be dynamic.

If you know of a better way of doing this, maybe a sort of vlookup in the data validation drop down or something that would be fine it the named range thing wont work.

I have attached a file showing this if its any help.

Its just a methord of getting the drop down to work based on a selected part of a long list.

I am an ameture at VBA but can knock a bit of code so if thats my best bet let me know.

Thanks for any help in advance.

Harry P

[StephenR]

See the attachment for one method. Run the macro and check the named ranges.

[Harry_Potter]

that works great, can you let me know what each line does so I can mess with it a bit? if you have the time.

I will be open to other solutions if anyone has any out of intrest.

[Harry_Potter]

so far i have worked out you filter to get unique 'departments'
copy and paste this list in the K column
then loop for the number of departments, selecting one filtering by that department and appling the named range.

Then moving on to the next department until they are all delt with.

then remove all filters and delete k column.

I couldn't replicate it but think im getting the feel for it, clever stuff Thanks

[StephenR]

This uses advanced filter to copy a list of the unique departments in col A into K1 (and down).

Please Login or Register  to view this content.

Loops through each of these unique departments

Please Login or Register  to view this content.

Turns off autofilter in case it's on

Please Login or Register  to view this content.

Filters column A by the department

Please Login or Register  to view this content.

Takes the filtered range minus the header row and resizes to the second column (B) only i.e. the jobs and names it the same as the department name

Please Login or Register  to view this content.

Removes the filter on column A

Please Login or Register  to view this content.

Removes autofilter altogether once each department looped through

Please Login or Register  to view this content.

Removes the department list

Please Login or Register  to view this content.

One thing I've learnt in Excel is that there is almost always more than one way to skin a cat so others may come up with different approaches.

[Harry_Potter]

This does work a treat, but when my little system is up and running none VBA people will be using it so if there in another way of doing this that would be a big help but this does exactly what I need Thanks.

Now if only I could fit a custom list in to the drop down like you can in access by using two fields so the drop down displays

Manager | Accounts Department

But only displays Manager when selected, this alows you to give additional info when the user picks a value.

Its handy when using initials so you may have HP and HAP with HP = Harry Potter and HAP = Harrold A Potter, you can help the user by giving the drop down options;

HP | Harry Potter
HAP | Harrold Potter

I would only need to record the initials as they will relate to another sheet and allows me to keep the amount of text on this large sheet to a minimum.

I think this is getting into the realms of combo boxes and thats going to make a mess of spreadsheet which is populated by a macro in the first place and so does not have a fixed number of cells for combo boxes, but I can apply a validation drop down to all valid rows.