Solved: Data Validation Drop Down - Changing the parameters

[Hoopsah]

Hi

as per the attached sheet.

If I have a list of peoples names in column A and their department in column C.

If I create a drop down validation list for department name, can I then have another drop down that would only include the people from that department.

I have done this before using the INDIRECT function but, my big problem is that the list will constantly be getting updated with people either leaving, joining or changing departments, so I am unable to specify a range as tomorrow that could have changed.

Thanks for any help

[Bob Phillips]

Is this what you want?

[Hoopsah]

That is amazing Bob.

I have been trying to get this for 4 days now.

What can I say except thank you once again.

Marked as solved - this EXACTLY what I was trying to get,

Cheers Bob

Gerry

:bow: :bow: :bow:

[Hoopsah]

Hi Bob,

sorry to ask this - But....

Within your formula in scratch worksheet there is the part "_dept_ID=_Chosen_Dept"

Now I understand this, but what I am wondering is, is it possible to have the Chosen Dept field point to more than one cell?

The project I am working on will have a few sheets where I hope to put these dropdown options?

Cheers Bob

Gerry

[Bob Phillips]

If the department dropdowns are in a range on the same sheet, that is quite straight-forward, on m ultiple sheets, very difficult.

[Hoopsah]

Cool

it will be on multiple sheets - I will just make the scratch page for all my pages and re-name chosen dept1 etc.

Cheers Bob, just wanted to ask before I got to far ahead doing it only to find there was a simpler way (As I usually do)

Cheers

Gerry

[Bob Phillips]

What we could do is have the separate dropdowns on each sheet and setup a link for each to a range of contiguous cells on the scratch sheet, and build it there.

Where will the list of employees show? Can you post an example book?

[Hoopsah]

Hi Bob,

I think I have got it - just using the same formulas and named ranges as you had done but incrementing them by 1.

I'll post a copy to let you see

Gerry

[Bob Phillips]

I see Gerry, I was thinking you wanted something else. I thought you were suggesting thst you would have say 3 dept dropdowns, and you would select a value in ecah of these, and then you wanted an employee list that covered all 3 departments.

By the way, Willaim McNulty is duplicated in the data.

Are you enjoying your VBA expoerience, or is it just something you have to do to get the job done?

[Hoopsah]

I'm lovin it Bob.

I went to college years ago and done programming in Cobol. Pascal & C++ then through a series of bad career moves I ended up not programming again.

When I got this job and realised that I had to use Excel, which I was unfamiliar with - I always used Lotus 123 - then I got as many books as I could and was constantly searching the web.

I know that you have been more help than all the books put together, and the project that I am currently working on (The one I have been posting) is just my little fun thing. I find if I can get the pages to do what they are told then I understand the formulas for doing a project that is forced upon me.

I'll check out that William McNulty too

Cheers Bob

Gerry

[Bob Phillips]

I thought that you were.

I did Cobol and Pascal too. I tried C++ but it was too hard, even though I started in Assembler, I found C++ required too much work.

I too started in Lotus 1-2-3 (actually I went from Visicalc, to SuperCalc to Lotus), and only migrated to Excel when it was adepartment policy. For quite some years I felt it was inferior to 1-2-3, and then they added VBA in Excel 97, and well, the rest is history.

I find learning from books tough going, hands-on works so much better.

[Hoopsah]

I think the thing about books is they very rarely answer a specific question.

When you show me how to do something - like the validation - I will keep a copy of it in my own folder and then the next time it crops up I know I have a working version other than trying to remember what I did the last time in what program and from which book I gleamed the info.

I must admit, I preferred Pascal, but C++ was much faster - but I learned in Pascal first, probably why I like it.

[Bob Phillips]

I like Pascal too, it is similar to Algol which I started on. But I admit that when I had to use Pascal recently, to write some code to automate addin installation in Inno Setup, I struggled (thank goodness for t'internet).

[Hoopsah]

Lol!

I said that I liked it, but, same, I don't think I would know where to begin if I had to use it now.

That said, this has been the best forum I have been on for VBA - I'm sure there must still be some for Pascal too. But I'll leave that to others to find out.