Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Do you know how to use VBA? you can do borders by recording a macro. I'll look up what I have for moving validators. AFAIR its in VSTO c#. –  Jeremy Thompson Aug 14 '12 at 5:48
    
@JeremyThompson: My VBA skills are modest - I'm actually learning VBA as I go for this particular job. Borders don't bother me, it's the data validation that's important. I don't want it accidentally munged if someone clicks the "Sort" button. –  Li-aung Yip Aug 14 '12 at 6:12

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
A good approach if you have a small number of possible rules, which is what my example showed. :) In the case of possibly hundreds of distinct rules, though, it's unwieldy to build up a single, complex validation rule. It also means that the input message and error alert can't be very informative. –  Li-aung Yip Aug 14 '12 at 18:26
    
@Li-aungYip Yes, those are valid problems given the constraints. I know nothing about your project, but maybe the table design is making the problem worse? That might be a good place to start looking for ways to minimize this problem or avoid it altogether. If redesigning the workbook is not an option, and you need brief and informative error messages for your data validation, then I'm afraid a (complicated) event-trapping VBA procedure is the only solution I see. –  Excellll Aug 14 '12 at 18:59
    
Unfortunately this is one of those jobs where we're trying to finagle Excel into being a real database application, which it isn't. Ideally I wouldn't be using Excel for data validation this complex at all, but given the end-users of the spreadsheet will be miners and electricians, it has to be in Excel or they haven't a chance of using it effectively. –  Li-aung Yip Aug 15 '12 at 2:04
    
Upvoting and accepting this because it's the most helpful answer for people with this problem in future (even if it didn't help me.) –  Li-aung Yip Aug 15 '12 at 2:09

Looking at bits of old code, it appears I deleted and recreated the validators:

var cellValues = activeSheet.Range[leftColumn + startingRow, Type.Missing];
cellValues.Validation.Delete();

If you want to do anything in Excelprogramatically, record a Macro > do the operation manually > Stop the Macro recording > press Alt + F11 and view the code in the newly created Module. This is a quick way to pickup VBA programming. Good luck!

share|improve this answer
    
Not quite what I need - the data validation is not uniform within rows or columns. If you imagine every cell having unique validation rules then you're pretty close to what my spreadsheet has. :( –  Li-aung Yip Aug 14 '12 at 7:33
    
This is the right idea, but you'll need to incorporate some way to match the validation rules to the new cell addresses in the sorted data. This is no trivial task, esp. with 100+ validation rules. –  Excellll Aug 14 '12 at 19:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.