5

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.

2
  • 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#. Aug 14, 2012 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. Aug 14, 2012 at 6:12

3 Answers 3

3

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

4
  • 2
    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. Aug 14, 2012 at 18:26
  • 1
    @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, 2012 at 18:59
  • 1
    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. Aug 15, 2012 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.) Aug 15, 2012 at 2:09
0

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!

2
  • 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. :( Aug 14, 2012 at 7:33
  • 1
    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, 2012 at 19:06
0

This is not entirely an answer to your question, but I feel it should be mentioned.

The way your data is entered into the spreadsheet is a bit uncommon, and if there are no specific reasons you might also solve the problem by changing the design of the spreadsheet a bit. The most straightforward way to enter data such as yours is to have one column per key (Color, pH, Diameter etc) and one row per Fruit. That way you can define rules and styles for the whole column and won't have to worry about sorting.

If you need to access data using a key and a value, you might be helped by using pivot tables and pivot table lookup (GETPIVOTDATA) or row/column lookups (VLOOKUP / HLOOKUP).

In your case the keys seem pretty well defined (otherwise a reason to go with the key-value approach), and given that Excel allows thousands of columns you won't run out of keys either. Using a separate column for each key will also function as a kind of input form. You won't forget to enter a value for a fruit since the cell for that value will be empty.

If you have several samples (not all apples have the same diameter, etc.) you will be able to treat each row in the spreadsheet as a single sample.

This approach will also facilitate further processing of the data such as creating diagrams and pivot tables.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .