Excel - Conditional Dropdown List Solved

Question

Good evening,

I would like to create a dropdown list in Excel where the displayed content depends on the value of the previous cell (which itself is a dropdown list).

To be clearer, I have a list of services List_Services (Service 1, Service 2, Service n...) in one cell. I created this list from the "Data / Validation" menu. I would like the next cell to display a list of items corresponding to the selected service.

In short, I have in an Excel sheet (Sheet A) the following table:

 | A | B | C | D | E 1 | | Service 1 | Service 2 | Service 3 | Service n 2 | Service 1 | Item B2 | Item C1 | Item D1 | Item E1 3 | Service 2 | Item B3 | Item C2 | Item D2 | Item E2 4 | Service 3 | Item B4 | Item C3 | Item D3 | Item E3 5 | Service n | Item Bn | Item Cn | Item Dn | Item En

In another sheet (Sheet B), I have (simplified) the following cells:

 List_Services | List_Item

Once the service is selected in this sheet, I want the List_Item to display based on the choice made (that is, based on List_Services). If I select Service 2, I want only the list for Service 2 (column C of Sheet A) to show up.

I’m not quite sure how to do this. The lists are defined.

I hope I have clearly explained the problem I have so that someone can help me out.

A big thank you in advance to whoever will help me solve this issue.

Best regards.

Configuration: Windows XP Firefox 2.0.0.11

Cyrilounet · Accepted Answer

Hello,

Actually, I created my lists in one sheet (Sheet_List), as follows:

 | A | B | C | D | E
1 | | Service 1 | Service 2 | Service 3 | Service n 
2 | Service 1 | Symptom 1_1 | Symptom 2_1 | Symptom 3_1 | Symptom n_1
3 | Service 2 | Symptom 1_2 | Symptom 2_2 | Symptom 3_2 | Symptom n_2
4 | Service 3 | | Symptom 2_3 | Symptom 3_3 | Symptom n_3
5 | Service n | | Symptom 2_n | | Symptom n_n

I then named each list:
- listServices for B1:E1
- ERR_list1 for B2:B3
- ERR_list2 for C2:C5
- ERR_list3 for D2:D4
- ERR_listn for E2:E5

In my main sheet (Sheet1), in the cell where I wanted to display my first list (E2 for example), I created a list as follows:
- Data menu / Validation;
- in the "allow" section, I selected "List";
- in the "Source" section, I entered the following formula: =listServices then OK.

In the next cell F2 (cell whose display depends on the choice of the previous cell), I proceeded as follows:
- Data menu / Validation;
- in the "allow" section, I set it to "List";
- in the "Source" section, I entered the following formula: =INDIRECT("ERR_"&(SEARCH(E2,listServices)))

And that's it (if I haven't forgotten anything). If you need more explanation or help, feel free to ask ;-)

Best regards

cyrilounet · Answer

Well, I've found a solution that consists of displaying a so-called dynamic list based on the selected service :-)  If anyone is interested, feel free to reach out.  Take care!

dezie · Answer

HELLO,
I have a little problem:
I have a customer database on Excel? I recorded the customers who buy and renew subscriptions. But the problem is that when a customer renews their subscription, they appear multiple times in the database. But I don't want to delete them because I need to keep the start and end dates of their subscriptions. How can I keep their name once on a single line and show their subscription dates? I thought about a dropdown list that would display all the information about the customer. But is this possible?????? I hope I explained the problem well.
I hope someone can help me!
Thank you!

michel_m · Answer

Just in case, I would like to point out that the tutorial exists in the "tips" section of this site...

michel_m · Answer

Hello,

Attached is a cascading list with 2 validations
https://www.cjoint.com/?fus33qE7Ds

Cascading list with only 1 validation (solution via VBA)
https://www.cjoint.com/?fus5ZEpkXy

Data validation helps avoid handling errors in formulas

For the INDIRECT function (Excel's "magic" function), refer to the help documentation

Have a good evening

Michel

michel_m · Answer

Okay, I was thinking about the "non-vba"  I don't know (it's an old thing from my attic) I need to check the beast, but certainly at home, tonight or this weekend.  Michel

JR2Mars · Answer

Hello, I have a similar issue, if someone could help me, I have the file if you want (or feel free to resolve it yourself ^^);
actually, my operation involves calling a headset reference in a dropdown list (or validation, it doesn't matter) and from there, having in another dropdown list (or validation) the available accessories for that reference...
Please help me, I can't take it anymore
thank you in advance

lorelei · Answer

Hello,
I am facing the same problem as cyrilouet a few months ago and have followed your email exchange. That said, I cannot open the links from my work computer. I have also looked at the tips but have not found the solution to my problem. Would it be possible to receive the workbook created by Claben by email?
Thank you in advance
Lorelei

yohann · Answer

Hello everyone,I am currently working (myself) on conditional dropdown lists and I am faced with a small problem that may not be a problem for you.In other words, my question might be silly: How can I hierarchize the data contained in a conditional dropdown list without having to sort the source data?Another question: how can I prevent empty cells from appearing in this dropdown list, which are present in the source data list?I would appreciate receiving an answer... Thank you.

celine · Answer

Hi everyone
It seems to me that you have solved this problem well, and now I would like to know the solution, but I can't open the links from the tutorial sent by Michel. How can I view them?
Thank you

BE YOURSELF THERE IS ENOUGH OTHER

michel_m · Answer

Hello Celine https://www.cjoint.com/?hgqp3FU7Uv  Michel

celine · Answer

Hello everyone
Thank you Michel for your super quick reply :)
But unfortunately, I can't solve my problem, and on top of that, I have a very large amount of data. Is there a solution in VBA?

Thanks in advance

jé · Answer

Hello, sorry to ask for this file again, but I would like to take advantage of the solution and the expiration date. Could you please update it? Thank you.

wilfried_42 · Answer

Hello

here is a slightly different file, as I don't like to put formulas in a validation list, I put them in named ranges

a named range: Brands, with a dynamic list management formula (you can add as many entries as you want)
a named range: Types, with a sliding and dynamic range formula (you can create as many types as you want): Sliding: the range moves based on the value entered in C6

here is the link: Lien Cjoint.com

wilfried_42 · Answer

re:  I think I have a problem with the link, I don't know how to do it lol https://www.cjoint.com/?ivsK7lCMRV   -- Best regards Wilfried

michel_m · Answer

Hello,  The links are valid for 21 days on Cjoint.com....  Attached is a demo of a cascading list https://www.cjoint.com/?krp4m6bPoZ -- Michel

michel_m · Answer

re,

I'm overwhelmed tonight!!!

I'm sending you something with 2 waterfalls that I had tinkered with for the CCM forum. Check if there are any processes that interest you (there's a breakdown of the formulas to satisfy the curiosity of the requester)
https://www.cjoint.com/?krr1xHyN0S
--
Michel

FRAVAI · Answer

Hello

As a beginner in VBA, I have a problem
I want to create a dropdown list in a cell that returns a numeric value based on the selected answer:
for example: dropdown list wordA wordB wordC which displays in the same cell: 1 if wordA is chosen, 2 if wordB is chosen, and 3 if wordC is chosen

Thank you for your help

michel_m · Answer

Hello Fravai,

Your question has one characteristic: IT IS VAGUE

If it concerns a data validation list that is the topic of this post, then there is no need for VBA...., a MATCH function is enough (check the help)

Otherwise, if it's about a dropdown list from the form toolbar or the combo box from the "control" toolbar, you need to specify that, feel free to create another post if needed in the programming forum.
If you are new to VBA, consult this very educational and relaxed site:
www.info-3000.com
but it's better to have a good understanding of major Excel formulas and functions (like the index-match combination, OFFSET, SUMPRODUCT, INDIRECT, array formulas...) before diving into VBA
--
Best regards,
Michel

michel_m · Answer

Hello,  Attached are some demos of data validation lists http://www.cijoint.fr/cjlink.php?file=cj200812/cijUN62mwY.xls  Some use VBA; set the macro security to medium (tools-macros-security) -- Sincerely, Michel

Runsh · Answer

Hello everyone,

From my work PC, I can't access the links, so I can't read the solutions to the issues.
I am facing the same problem of making drop-down lists chain based on the previous cell, which is itself from a drop-down list.
Would it be possible for someone who has a file in which what is a problem for me works to send it to me please?
If possible, without using VBA code but with regular formulas... I don't have a grasp of coding :-(

My email address: d.cantaloube@aliceadsl.fr

Thank you very much in advance for your help!

David

mathbou73 · Answer

Hello everyone I'm very interested in this topic and I would like to know the solution as well, but I can't open the links to the tutorial sent by Michel. How can I access them?  Thank you in advance

michel_m · Answer

Hello  Please find attached the beast https://www.cjoint.com/?eqgySzCwMZ -- Best regards, Michel

titi90 · Answer

Could someone send me the link again because it is no longer active. Thank you.

michel_m · Answer

Saying hello is not mandatory of course http://www.commentcamarche.net/contents/ccmguide/ccmcharte respect autrui.php3#politesse  for the file https://www.cjoint.com/?gmkNPn6vD1 -- someone!!!...

annecou · Answer

Excuse me for not replying right away, but your idea really helped me thank you very much

vodkaddictgeii · Answer

Camille Etienne · Answer

Hello, I'm still stuck with this topic. Most of the links are disabled and I'm having a hard time navigating through the file with the examples. I see that "michel_m" is very active and seems to know a lot. Could you help me with a solution to this problem? Because even after reading through the entire topic, I'm not able to figure it out. My problem: the one about a dropdown list that depends on the first one. And I have the same issue with the "INDIRECT..." function; it tells me that the field is incorrect and my list is empty. Thank you for your responses. Cam

Mytå · Answer

Hello forum  Try to share your file  Or check out this discussion  To attach a file, create a link using https://www.cjoint.com/ or http://cijoint.fr/ and then copy the link into your next message.  Mytå Thank you for following up on your question, we are not robots... Installed versions [MsProject 2003(FR), Excel 2003-2007(FR)]

Camille · Answer

Here is my file, I hope you'll find your way around it. https://www.cjoint.com/?iDlQK6WjXS Now it finally works but I didn't know that the name of the list had to be exactly the same as the previous element. Now I would like to extend my functions up to line 22. If I do it like extending a formula, it's not going to work well... maybe I'm handling everything wrong? Also, in the link you gave me, what is the purpose of the OFFSET function? And what is COUNTA? Thank you, have a nice Sunday Cam

piratman · Answer

Thank you very much to Michel_m for the "list box" file, which is great! +++

Nobru · Answer

Hello, Regarding the cascading dropdown lists, my concern is that in the first list, there are values that should not point to another dropdown list but allow the user to enter their own value. Is this possible?  Example: depending on what is chosen in the dropdown list in Cell A2 = a list: Starter-Main Course-Cheese-Dessert / the next cell will offer the associated list EXCEPT for the case of the bill where the amount will be entered If A2="Starter" then B2="vegetables-egg-charcuterie-melon" If A2="Main Course" then B2="fish-meat-chicken" If A2="Cheese" then B2="picodon-camembert-roquefort" If A2="Dessert" then B2="fruit-ice pastry" If A2="Bill" then B2=<free to enter whatever one wants>  See you!

mascio · Answer

You enter the Source field (Data\Validation\List):  =OFFSET(list_Item,MATCH(A1,list_Services,0)-1,,COUNTIF(list_Services,A1))  by replacing A1 with the address of the cell in which you made your service selection.  The 'list_Services' list must be sorted for this to work.

pilote360 · Answer

Hello, I'm sorry I'm a bit late --' but I have a question, does anyone know a trick to add spaces between names?  Thank you for your attention ....

michel_m · Answer

Hello attached is the spreadsheet https://www.cjoint.com/?3Hytkw3rzmc --  Michel

MyoMao · Answer

Hello,  I've been working on this conditional dropdown list issue for days and I'm about to crack!  I understand the manipulation, but when I try to create my second list, I keep getting that same error message that many have encountered before me. I still don't see where it could be coming from.  I can't download the attached files, I'm at work, and anyway they're no longer available.  Could someone kindly explain the steps to take clearly, here, without using an attachment please???  I've tried with Excel 2007 and the INDIRECT function, result: error message. I've tried with Excel 2010, and still an error message!  So either I'm clueless, or I'm missing a step in the process!  I really need this, please help me!!!

moi_z_oci · Answer

Hello Michel... Would it be possible for you to re-upload the file please? Thank you

michel_m · Answer

Hello https://www.cjoint.com/?3EllOizFaf3  --  Michel

michel_m · Answer

Hello  http://www.cjoint.com/c/ELbqvTJ6oQQ  --  Michel

Excel - Conditional Dropdown List

39 réponses

Canon mb5350 - cartridge replacement impossible

Texture issues in enshrouded

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.

Wake on lan with livebox

"freebox" is blinking (tv)

How to access the bios

[freebox] what customer service after lightning strike?