Put 3 items in a drop down menu. When I sorted the drop down all the values disappeared with the exception of one.

Forgot to mention that it's an excel chart.

Kind of hard to imagine what is happening here. How about posting a sample workbook!

I attached the excel spread sheet. The text in red hold data that is in a pull down menu. When I sort the column this data is in, the cells with the pull down menus no longer stay as pull down menus.

Attached Files

Authors Query Library.xlsx (264.3 KB, 15 views)

Sorry, not sure how to do this. Maybe somebody else with this type of Excel knowledge will stop by.

Goodness, I didn't realise Excel wouldn't carry data validation in cells with sorting. What a pain. There's no easy solution that I can find.

There is a VBA solution but it's clunky. It would involve, before sorting, putting (temporarily or permananently) the list used in data validation in, say, column I of the same row as the data validation in column A (this column could be hidden), then sorting the list including column I, then running down column A re-establishing the data validation from the information in column I (which could include the message '3 authors, 1 book', '5 authors, 1 book', 'multiple authors, 1 book').


I'll write it if it would be useful and a path you're happy to go down.

Data validation is not meant for multi-entry, it is for alternative choices!

An example of simple books database

Attached Files

Library.xlsx (165.6 KB, 13 views)

Thanks to all who responded. Thank you Arvilaanemets -- I'm following the example that you attached. It was very beneficial.