“Data Validation List” How Do You ADD 3 Lists Of Names, Into 1 Drop Down list?

[StickCity]

I'm trying to make a shift labour request book, using drop down validation lists and I'm struggling with the last drop down! What I want to do is look at the Duty shift code, which can be up to 45 codes of ABC; BCD; DEA; AND AB; CD; and so on. In the drop down list for Covered BY the one I’m struggling with. I want to look at the duty shift code and the get the names of the shift letter and put them into the Duty shift.
For example;

Duty Shift code ABC ( which stands for A shift, B Shift and C Shift)


Covered By

Would have a list of names from A shift, B Shift C Shift
And the list would look like this,

Jim
Paul 1
Martin 1
Rob
John 1
Pong
John 2
Martin 2
Jeff
Ian 1
Addam
Phil 2
Stew
Gerry
Gave
Barry

Cossey
Garry
Sid
Paul 3
Andyv1
Hason
Tim
Brenden


I can do this for one date only so far and if try another way I run into the problem of data validation list has too many characters.
Can anyone have a look and help please.

[mikeTRON]

Where in this are you having an issue?
I cant really follow what is going where quickly and I don't want to trace through the entire thing.

[mikeTRON]

I think I would create a list for EACH combination (ABC, BDE) using logic or whatever, then just define them with the exact named range and you are done.

In the past I created the ONE data table (or lookup table) which has a column for each A, B, C, D, E, Days or whatever THEN you can just wrap a pivot on THAT and build a single column pivot on each combo. it might take 10 minutes to setup, but it is infinitely easier to manage than

Please Login or Register  to view this content.

which IS clever, assuming it works, but not as easy or quick to audit.

[StickCity]

I have tried creating a list for each combination and using if logics to look up each of the combinations in the data validation. But found that I ran into the problem of the data validation list source has too many characters in it! As yet I don't know how to wrap a pivot table around the data table your on about. I do have the lists there already. Also these validations will have to work on a my labour book that is going to be about a 1000 rows deep, covering 365 days a year! I have to look at pivot tables and learn how to do what you are describing.

[mikeTRON]

Are you saying you dont know how to make a pivot table?
That is the easy part, its getting ALL of the data together to make ONE master table to map everything, then the pivots are easy (although you might need 45 of them if you want to keep things simple).

[StickCity]

I'm still learning and I have hardly touch on pivot tables. I'm looking into pivot tables to see if it will work.