Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

HELP_DEPENDENT DROP-DOWN LISTS

Brass Contributor

Hi everyone !

 

Here is my dependent drop-down lists PROBLEM:

- I have 4 LEVELS (3e, 4e, 5e, 6e) in DATA VALIDATION

- Each LEVEL has 6 CLASSES (A, B, C, D, E, F) example: 3eA, 3eB, 3eC, 3eD, 3eE, 3eF)

So 24 CLASSES

 

I want once I select a LEVEL in (J2) from its drop-down lists I can also choose its CLASSES in (J3)

Example: for (J3) DATA VALIDATION be =(IF (J2)= "Class_3e") so (J3) = 3eA;3eB;3eC;3eD;3eE;3eF

               for (J3) DATA VALIDATION be =(IF (J2)= "Class_4e") so (J3) = 4eA;4eB;4eC;4eD;4eE;4eF

               for (J3) DATA VALIDATION be =(IF (J2)= "Class_5e") so (J3) = 5eA;5eB;5eC;5eD;5eE;5eF

               for (J3) DATA VALIDATION be =(IF (J2)= "Class_6e") so (J3) = 6eA;6eB;6eC;6eD;6eE;6eF

So that Each (J3) choosen CLASS fills "CLASS LIST" table (with yellow color)

 

File below:

MJrv4dnUZjI_HELP-DEPENDENT-DROP-DOWN-LISTS.xlsx

 

Thanks !!!

11 Replies

@Boulakgnobot Changed the names for the list ranges so that you can use =INDIRECT(J2) in cell J3. See attached. 

 

Saw in your file that you have some more questions but I didn't understand your intention there. Please clarify.

Thanks for your help ! It's EXACTLY what I WANTED for the first point !

I'm not an expert and I don't know how you named (J2) to make it changed through the column

3eA 4eA 5eA
3eB 4eB 5eB
3eC 4eC 5eC

Because I don't see anywhere the names for the list ranges (not in the NAME BOX and not in the NAME MANAGER)

HOW do you do it so that I can redo it ???
CAN you EXPLAIN this more ???

@Boulakgnobot Not sure where you are looking. I'm on a Mac right now but it's similar on a PC. 

In the name box, I see this:

Screenshot 2023-10-18 at 19.49.59.png

and in the name manager I see this:

Screenshot 2023-10-18 at 19.51.20.png

Don't you see the same?

I did ! What I don't understand is "=indirect(L2)" in DATA VALIDATION

 

EXCEL_HELP_Indirect L2.jpg

@Boulakgnobot J2 contains the selected class by its name. Then, =INDIRECT(J2) points the validation list to the named range which name is in cell J2. 

THANKS ! THANKS ! Now I get it !

NOW CAN you help with the POINT 2 ???

FILE above

https://www.cjoint.com/c/MJtwF6cLaXs

 

EXCEL_HELP_FILL CLASS_LIST 02.jpg

@Boulakgnobot I hope you are on Excel365 or 2021 as this is easy with the FILTER function. See attached.

Thanks ! I mistake with orientation ! Instead of "above" file it was "BELOW" NEW FILE with the PICTURE illustration

 

I'm SORRY !

 

YES I'm Excel 365

 

https://www.cjoint.com/c/MJtwF6cLaXs

 

PS: If you CAN HELP again

@Boulakgnobot Sorry, don't understand. Looks like a very similar file (if not the same) as the previous one.

What remains is the cumulative table of lists maybe with "FILTER" or "VSTACK" FUNCTIONS and PULL OUT empty spacies (zeros) and "SORT" (FUNCTION) by {last name;name}

 

https://www.cjoint.com/c/MJtwF6cLaXs

 

EXCEL_HELP_FILL CLASS_LIST 02.jpg