Excel validation range limits

Question

When Excel saves a file, it attempts to combine identical Validation settings into a single rule with multiple ranges.

This creates one of three issues, depending on the file type you choose to save:

When saving as a standard Excel file (Office 2000 BIFF), a maximum of 1024 non-contiguous ranges that can have the same validation setting.
When saving as a SpreadsheetML (Office 2002/2003 XML) file, you are limited to the number of non-contiguous ranges that can be represented, comma-delimited in R1C1 format, in 1024 characters.
When saving as an Open Office XML (Office 2007 *.xlsx), there is a maximum of 511 non-contiguous ranges that can have the same validation setting. (I don't have Office 2007, I'm using the file converter for Office 2003).

Once you bust any of these limits, the remaining ranges with the same Validation settings have their Validation settings wiped. For (1) and (3), Excel warns you that it can't save all of the formatting, but for (2) it does not.

Not a question. Just documenting a discovered bug and workaround to the Google Fairy will help the next person. Guess I should add my own answer and accept it. — richardtallent
– richardtallent, Commented Dec 9, 2009 at 20:10
I've done that before also, I just try to word the question as a question. +1 for the info. — Lance Roberts
– Lance Roberts, Commented Dec 9, 2009 at 20:31

richardtallent · Accepted Answer · 2009-12-09 20:10:58Z

2

The workaround is to make the Validation settings for each range somehow different (change the error message, etc.) so Excel does not attempt to combine them. Solution tested in Office 2003 for all three file types, works as expected.

answered Dec 9, 2009 at 20:10

richardtallent

35.5k14 gold badges87 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel validation range limits

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related