SOLVED

Creating a drop-down list

Copper Contributor

Hi, could you advice me with drop-down list.

I have created drop-down list in excel.

The first level list contains 31 items.

The second level list contants 1-20 items for each item from first level.

The drop-down list works fine, except of 3 items from first level list.

This 3 items do not react at all. They will show no items for second level. They are inactive

 

I have created drop-down list from schratch again. The 3 items form first level are inactive again, but they are different ones than before. 

 

Thank you

Katarina

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Katarina_Molnarova 

It sounds like you are trying to create a dependent (or cascading) drop-down list in Excel 365 where the options in the second-level list depend on the selection made in the first-level list. The issue you are facing with certain items in the first-level list not working as expected might be due to data validation settings. To resolve this issue, follow these steps:

  1. Check Data Validation Settings:
    • Select a cell where you have created a first-level drop-down list.
    • Go to the "Data" tab on the Excel ribbon.
    • Click "Data Validation" in the "Data Tools" group.
    • In the "Data Validation" dialog box, make sure you have set the "Allow" field to "List."
    • In the "Source" field, ensure that you have correctly referenced the range of the first-level list options.
  2. Check for Data Errors:
    • Examine your data source for any potential issues, such as extra spaces or non-printable characters in the cell values. Inconsistent formatting or spacing can cause problems with drop-down lists.
  3. Use Named Ranges:
    • To make your data validation more robust, consider defining named ranges for both the first-level and second-level lists. This can help prevent issues caused by expanding or changing the source data.
  4. Clear Existing Data Validation:
    • If you have made changes to your data validation settings, ensure that you have cleared any previous data validation settings that might conflict with the new settings.
  5. Recreate the Drop-Down Lists:
    • Sometimes, recreating the drop-down lists from scratch can help resolve issues. Delete the existing data validation settings and start over.

If you're still experiencing issues with specific items in the first-level list not working as expected, double-check the data source and the data validation settings for those items. Also, ensure that there are no hidden characters or inconsistencies in your data source that might be causing the problem.

If the issue persists after following these steps, please provide more details about your setup, and I will be happy to assist you further. The text and the steps was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

 

Thank you @NikolinoDE. The problem was caused by the item in the first level containing commas. After removing commas, second-level list works perfectly. Thank you one more time.

Thank you for your feedback.
I'm glad to read that you were able to solve your problem.
I wish you much success with Excel!
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Katarina_Molnarova 

It sounds like you are trying to create a dependent (or cascading) drop-down list in Excel 365 where the options in the second-level list depend on the selection made in the first-level list. The issue you are facing with certain items in the first-level list not working as expected might be due to data validation settings. To resolve this issue, follow these steps:

  1. Check Data Validation Settings:
    • Select a cell where you have created a first-level drop-down list.
    • Go to the "Data" tab on the Excel ribbon.
    • Click "Data Validation" in the "Data Tools" group.
    • In the "Data Validation" dialog box, make sure you have set the "Allow" field to "List."
    • In the "Source" field, ensure that you have correctly referenced the range of the first-level list options.
  2. Check for Data Errors:
    • Examine your data source for any potential issues, such as extra spaces or non-printable characters in the cell values. Inconsistent formatting or spacing can cause problems with drop-down lists.
  3. Use Named Ranges:
    • To make your data validation more robust, consider defining named ranges for both the first-level and second-level lists. This can help prevent issues caused by expanding or changing the source data.
  4. Clear Existing Data Validation:
    • If you have made changes to your data validation settings, ensure that you have cleared any previous data validation settings that might conflict with the new settings.
  5. Recreate the Drop-Down Lists:
    • Sometimes, recreating the drop-down lists from scratch can help resolve issues. Delete the existing data validation settings and start over.

If you're still experiencing issues with specific items in the first-level list not working as expected, double-check the data source and the data validation settings for those items. Also, ensure that there are no hidden characters or inconsistencies in your data source that might be causing the problem.

If the issue persists after following these steps, please provide more details about your setup, and I will be happy to assist you further. The text and the steps was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

 

View solution in original post