Data validation doesn't work after sort

Question

Hi, I have an Excel spreadsheet that I want to use with drop down lists for validation.  Once I set up the data validation, and sort the sheet different ways, the validation doesn't follow the row.  Is there a way of creating a drop down list that will stay in the row?  For example, say I set up the following table: A column is the Category, B column is the item and C column are the choices that I can set up in a drop down list to enter using Data validation in the Feature column.

Exterior	Feature	Criteria for Data Validation
Drive system	Front wheel drive	Front wheel drive, Rear wheel drive, All wheel drive
Tire size	205	185, 195, 205, 215
Anti-lock brakes	All wheel	Rear wheel, Front wheel, All wheel

If I sort this column alphabetically, the drop down lists would no longer 'line up' with the proper feature, because the cells with the validation don't move with the row as it's sorted, it's referenced to a specific cell on the sheet which doesn't move:

Exterior	Feature	Criteria for Data Validation
Anti-lock brakes	All wheel	Front wheel drive, Rear wheel drive, All wheel drive
Drive system	Front wheel drive	185, 195, 205, 215
Tire size	205	Rear wheel, Front wheel, All wheel

Is there any way to keep a drop down list in a row or anchored in some way so that when the sheet is sorted it 'travels' with the rest of the row?

Answer 1

Hi Paul,

Thank you for your detailed reply, so that I can understand your situation more clearly now, also I’m sorry for giving an inappropriate suggestion in my last reply.

After some tests, I think I found a solution, you just need to put the Data Validation list source range to the same rows as the data you want to sort.

For clarity, you can see the effect in the screenshot below:

Before: I set the list source as “A1,A2,A3”,”B1,B2,B3”,…,”E1,E2,E3”, and align them on the same rows as the data, then convert them to a table.

 

After: I changed “Column1” to sort from largest to smallest, and you can see that the reference to the pull-down list moves with the sort.

 

Hope the above method can be feasible for you, and please let us know the results if you have tried, your response could help those who have the same problem.

Best Regards,

Arck

Answer 2

Hi Bill,

I tried right clicking in Column A. It sorts, but just that column (there are gaps, so not a good example).  So I tried right clicking in Column B, and sorting A-Z loses all of the pull down references as before.  I also tried selecting all of the columns, and the issue persists. Here's a screen shot before the sort:

And after:

What I'm after is the pull down menu to 'travel' on a sort with the feature, so that "Type of propulsion" pull down will stay with "Type of propulsion". As you see from the second screen grab, it stays where it was and is now associated with "AC accessory outlet"

The sheet sorts just fine. It's the pull downs that don't move with the sort is the problem.

Answer 3

Hi Paul,

Thanks for posting in the community, according to your description, I did some tests on my side. I can reproduce your situation only when I add filters to “column A” and “column B” but no filter to “column C”, if I add filters to all three columns, data validation will work properly after sorting.

Please check if you are consistent with the above, if not, you are welcome to share your file with us via Private Message, please be careful to share your data in public.

Best Regards,

Arck

Answer 4

I fully agree with Andreas... Not much can be determined by a screen shot. However, based on the description as you've written it I'm venturing a guess that you may be sorting Column A by selecting only the cells in it rather than sorting the range. If so, that explains why the other columns are not being sorted.

What happens if you right-click any single cell in Column A & choose the Sort command from the contextual menu? Or if you select any single cell in Column A & use the Sort tool on the Data tab?

Answer 5

It is far too complicated to recreate such a scenario. And if it does not match yours, then our solution will not work for you.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data and colored cells with the expected result.

At best make a copy of your original file and anonymize the necessary data. For this please download this file

https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1

Open your Excel file

Right-click on the sheet tab

Choose "View Code"

Press CTRL-M

Select the downloaded file and import

Close the VBA editor

Select the cells with the confidential data

Press Alt-F8

Choose the macro Anonymize

Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.

https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.