Excel – Fix Sorting Issues with Data Validation Rules

microsoft excelmicrosoft-excel-2007

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:

  • Borders
  • 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:

enter image description here

And after sorting (A-Z on "Fruit" name.)

enter image description here

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?

Edit:

Just to clarify, I have two separable concerns here:

  1. 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.
  2. 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.

Edit:

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.

Best Answer

You can make a general rule for handling pH values and apply it to the whole column of values. You just have to include a condition that checks Key1 to see if the value is a pH; if it is, check the criteria; if not, just return TRUE so anything is allowed.

For example, here is a custom data validation rule applied to C2:C6 (Value 1 column) in your table:

=IF(B2="pH",AND(C2>=0,C2<=14,C2=INT(C2)),TRUE)

This will limit pH values 0<=pH<=14 and only whole numbers. Since the rule is applied to the whole column, sorting will not affect the data validation.

enter image description here

You can handle borders in a similar way if you apply them using conditional formatting. Just apply it to all the data in the column with an appropriate formula rule, such as

=B2="pH"

enter image description here

Related Question