Cascading drop down lists in Excel
This is based upon another question I found here. So here it goes. I have this file if I select the location in one drop down I would get the list of names of that location in the other drop down. I have setup the sheet exactly as it stated in the training video but the second drop down is not working! Please help me. Thanks!
dropdownsample.xlsx
dropdownsample.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
You're very welcome.
It is very easy to miss something obvious because you have been looking at a problem for so long.
Glad it now works for you.
It is very easy to miss something obvious because you have been looking at a problem for so long.
Glad it now works for you.
Moreover you can also apply a conditional formatting rule to hide the Employee which is already selected from the drop down list in B2 for a specific location selected in A2 and which doesn't belongs to the new location when selected from the drop down list in A2.
i.e. if you select New York in A2 and select Kate in B2, so now if you select another location say New Jersey in A2, the previously selected employee Kate for New York will still be visible in B2 which shows the wrong employee in B2 as per the new location in A2.
To avoid this, make a New Rule for conditional formatting using the formula given below and while setting the format, go to Number Tab --> Custom --> and set the custom number format as ;;; (three semicolons).
Refer to the attached for details.
Hope this helps.
dropdownsample.xlsx
i.e. if you select New York in A2 and select Kate in B2, so now if you select another location say New Jersey in A2, the previously selected employee Kate for New York will still be visible in B2 which shows the wrong employee in B2 as per the new location in A2.
To avoid this, make a New Rule for conditional formatting using the formula given below and while setting the format, go to Number Tab --> Custom --> and set the custom number format as ;;; (three semicolons).
=COUNTIF(INDIRECT(SUBSTITUTE($A$2," ","_")),B2)=0Refer to the attached for details.
Hope this helps.
dropdownsample.xlsx
ASKER