Multiple Data validation in Drop Down Lists

Hi everyone,

Have been struggling for a while to get a document correctly formatted but with no success. Would very much appreciate all the help you can give me.


Ref Data.
- 1 column "X" with the values: A, B, C, D, E, F
- 1 column "ABC" with some values
- 1 column "DEF" with some values
- 1 column "123" with some values
- 1 column "456" with some values


Sheet:
- "X" field on A2 as drop down list
- Drop down list field on A3 with ref data from "ABC" and "DEF"
- "X" field is also on A4 cell as a drop down list
- Drop down list field on A5 with ref data from "123" and "456"


Problem:
- In Cell A2 if the user chooses A, B or C the drop down list in A3 should only retrieve values from column "ABC" and if the user chooses D, E or F the drop down list in A3 should only retrieve values from column "DEF".
In A3 cell the field has to be populated depending on the option choosen in A2 cell.

- In Cell A3 if the user chooses A, B or C the drop down list in A4 should only retrieve values from column "123" and if the user chooses D, E or F the drop down list in A3 should only retrieve values from column "456".
In A3 cell the field has to be populated depending on the option choosen in A3 cell.


Have been trying with INDIRECT, VLOOKUP ando some other random hints found online but none matches my expectations and problem.
Can you guys give me a hand please?

Thank you in advance

Please post a sample file not image) showing expected results.

Hi John,

Thank you for your reply.
Not sure how to post a sample file without formatting the sheet as I referred, trying to do a sample I believe it will be more confusing.

But maybe with the image it becomes a little more clearer what I'm trying to achieve.

Capture.JPG

Sheet:
- "X" field on A2 as drop down list
- Drop down list field on A3 with ref data from "ABC" and "DEF"
- "X" field is also on A4 cell as a drop down list
- Drop down list field on A5 with ref data from "123" and "456"


Problem:
- In Cell A2 if the user chooses A, B or C the drop down list in A3 should only retrieve values from column "ABC" and if the user chooses D, E or F the drop down list in A3 should only retrieve values from column "DEF".
In A3 cell the field has to be populated depending on the option choosen in A2 cell.

- In Cell A3 if the user chooses A, B or C the drop down list in A4 should only retrieve values from column "123" and if the user chooses D, E or F the drop down list in A3 should only retrieve values from column "456".
In A3 cell the field has to be populated depending on the option choosen in A3 cell.

PLEASE post a file:

Click "Go Advanced" then "Manage Attachments" to upload file.

Here it goes.

If I have understood your requirement

Created Named Ranges: ABC_1, DEF_1, ABC_2, DEF_2 corresponding to your column headings 1,2,3 and 4

For DV

in J4

=IF(AND(CODE($I4)<=67),ABC_1,DEF_1)

in L4

=IF(AND(CODE($K4)<=67),ABC_2,DEF_2)

Copy down

Master John,
Thank you very much, this will do the trick perfectly.

If you are happy with the result, can you please mark the thread as solved ("Thread Tools" at top of first post).

Done!
Thank you once again.

Hi guys,

The solution provided works like a charm, however, it would be great if it would be possible to escalate this formula to a more complex system.
The goal is instead of having 2 possible reference data columns stated here before as ABC_1 and DEF_1 have double that so imagine "GHI_1" and "JKL_1".

I understand the logic to be applied but when it comes to formulate that get's a little tricker because using the CODE formula I cannot just state less than X and greater than Y. For this I'll probably need to define the code range with the use of OR functions I believe, but already searched online for some help in order to do it myself but it's becoming a quest.

Attach send an excel already adapted based on John Topley example, only thing missing is the correct formula.

Any chances you guys can give me a hand?

Thank you in advance

Set the columns (Column1, Column2 as named ranges)

Then set DV for Column11 as

=IF(CODE($M4)<=67,Column1,IF(CODE($M4)<=70,Column2,IF(CODE($M4)<=74,Column3,IF(CODE($M4)=75,Column4,Column5))))

Repeat the above for Column 12 changing named ranges to Column6 to Column10

That's great John, thank you.

Still encountered a few issues with the fact of having more than one reference value starting with the same ASCI char code but resolved it by changing one to CAPS.

Much appreciated

Alternative


Named range DV_LISTS: 3 columns

DV_LISTS
A Column1 Column6
B Column1 Column6
C Column1 Column6
D Column2 Column7
E Column2 Column7
F Column2 Column7
G Column3 Column8
H Column3 Column8
I Column3 Column8
J Column3 Column8
K Column4 Column9
L Column5 Column10

DV for Column 11

=INDIRECT(VLOOKUP($M4,DV_LISTS,2,0))

DV for Column 12

=INDIRECT(VLOOKUP($M4,DV_LISTS,3,0))