When sorting a table or range in Excel, the cell values and basic formatting like bold, italic, colours etc. move, but the following things do NOT move:
- Data validation rules
My spreadsheet has per-cell data validation, so when I perform a sorting operation it moves values around but doesn't carry their validation with them.
Here's an example before sorting:
And after sorting (A-Z on "Fruit" name.)
Is there a way to make Excel move the data validation rules along with with the values and formats when Excel performs a sorting operation?
Just to clarify, I have two separable concerns here:
- Primary concern: If a user decides to click the Sort button, or the Sort option on a Filter column heading, this will trash my (complex!) data validation rules.
- Secondary concern: It would be nice if my worksheet could be sorted using the inbuilt Excel buttons for sorting (these are the most obvious ways to sort data). A separate macro just for doing a sort seems a bit hacky.
My eventual solution was just to disable sorting entirely, by protecting the workbook and disabling sorting for all sheets. You can press the "Sort" buttons but they do nothing.
As an additional safeguard, I added VBA that re-applies Protect Workbook every time the workbook is opened, and brings up a worksheet with usage instructions and warnings (such as "Don't sort this!".) The end-user can un-protect the workbook at any time, so this isn't foolproof, but I believe it's the best I can do.