Possibly multiple data validation lists??

[danbates]

Hi,

I have an issue and I'd like someone's advice on what's the best/easiest way for the following to be done.

In column D, I have a numbered value that is found via a VLookup formula.
(This is the QTY of the trays on that particular program)

In column E, I have a data validation list of all the palletisers we use at work.
(D, E, F, G, H, J, K, L, M, N, P and Azera)

Palletisers D, E, F, G, H, J, K, L, M, N, P have all the same QTY for the first 9 programs which are: 126, 63, 168, 216, 144, 162, 72, 180 and 90

but K and M palletisers have a few more QTY's which are:

K = 160, 208, 72, 180, 192, 81, 88 and 140.

M = 84 and 66.

Azera palletiser has the QTY's 390, 240, 192, 288, 270, 144, 133, 96 and 152.

So this is what I would like if it's possible some way.

If the QTY of 126 is in column D then a list of D, E, F, G, H, J, K, L, M, N, P is shown in column E.

If the QTY of 390 is in column D then just Azera is shown in column E.

and if the QTY of 144 is shown then a list of all the palletisers is shown in column E.

I can't have a formula because I don't have anyway of telling what palletiser the QTY is for. That has to be done manually by the operator. I just want to limit the options if possible.

I hope my explanation is good enough and any help would be much appreciated.

Thanks

Dan

 

[6StringJazzer]

Somewhere else in your file, create two lists in two columns:

A list of all palletizers. Highlight this list and name it List1.

A list of D, E, F, G, H, J, K, L, M, N, P. Highlight this and name it ListAll.

In column E, use Data Validation, Custom Formula. For row 1 use this formula (if your data starts in a different row, change D1 to the appropriate row)

=IF(D1=126,List1,IF(D1=390,"Azera",IF(D1=144,ListAll)))

and copy down through all your rows.

 

[6StringJazzer]

I am taking a second look at your question and I think that the three cases you gave were just intended to be examples. In that case you will need to add OR cases to your formula for each of the three cases.

 

[danbates]

Hi 6StringJazzer,

Thank you for your reply and yes the 3 quantities I gave were just examples.
Your formula works but for only for them 3 quantities.

Let me try and hopefully explain it better now I can see your formula.

List1 (Palletisers D, E, F, G, H, J, K, L, M, N, P) I need to show in column E when the QTY in column D is 126 or 63 or 168 or 216 or 162 or 72 or 180 or 90.

List2 (Palletisers Azera, D, E, F, G, H, J, K, L, M, N, P) I need to show in column E when the QTY in column D is 144

List 3 (Palletisers Azera, K) I need to show in column E when the QTY in column D is 192

I hope this clears up my question and any further help would be appreciated.

Thanks again

Dan

 

[6StringJazzer]

Here is the general strategy but I see contradictions in your descriptions so I doubt this is exactly what you need. However, you should be able to take this idea and correct the list of values in each set of {braces} to match the cases you want. The first set resulting in List1 is for all except Azera; the second is for Azera only; the third set resulting in ListAll is for all.

=IF(OR(D1={126,63,168,216,162,72,180,90}),List1,IF(D1={390,240,192,288,270,144,133,96,152},"Azera",IF(D1=144,ListAll)))

 

[danbates]

Hi,

I have tried your formula but it is coming up with the following error:

You may not use reference operators (such as unions, intersections, and ranges) or array constants for data validation criteria.

Any ideas please?

Thanks again

Dan

 

[6StringJazzer]

Sorry, I forgot about that restriction. I will have to experiment with this one. Might be able to set up named ranges for those also.

 

[6StringJazzer]

I had success with this formula. You will need to create named ranges for each set of values that corresponds to a set of choices you want to see in the dropdown. You also need named ranges for each set of choices, which I think you already did.
I am reading all your posts and the descriptions don't match so hopefully you can adapt this to what you need:

=IF(OR(B1=Value1),List1,IF(OR(B1=Value2),List2,IF(OR(B1=Value3),List3)))

List1 is the named range with a list of corresponding palletizers
Value1 is a named range that has a list of the values that correspond to List1

List2 is the named range with a list of corresponding palletizers
Value2 is a named range that has a list of the values that correspond toList2

List3 is the named range with a list of corresponding palletizers
Value3 is a named range that has a list of the values that correspond toList3

If you're still having trouble we'll have to find a way to share your file.

 

[danbates]

Hi,

That formula is perfect, thank you so much for all your help. It really is appreciated.

Thanks again

Dan

 

[6StringJazzer]

Glad we finally got it worked out!