Formats and Data validations changes are not sticking

[Vinod]

Hi All,

I updated data validations in one of my workbook(5000 rows have been using
in that particulat sheet with validation rules for all the cells in this
range) which is already contains data validations. earlier it pointing to
allow values >=0 now i updated it allow values >=0 and <=1 (0, 0.1, 0.2,
....1). and i copied this for every 13 rows in the 5000 rows range.

During save I ecountered with the following error
Excel could not save all the data and formaiting you recently added.

1. To return to your workbook without saving click cancel
2. If you want to continue saving changes without reparing the problem,
Click OK.

When I clicked on OK then I knew that No row having validation rule from row
no 656 to 5000 (earlier each cell in this range holds a validation)

Please help me out how can I resolve this.

For your reference: This file is not holding any VBA Macro

Regards,
Vinod

 

[Susan]

i have found that there is a limitation to the number of cells you can
have with data validation. i haven't been able to confirm that
through excel newsgroups, though, but i had the same error as you are
experiencing. it's possible when you copied the validation excel
counted them as NEW validations, not as replacements for already
existing ones. so now you're over the limit. maybe somebody else can
tell us what the actual limit is...........
the only way i worked around it was to start a brand new workbook &
copy & pasted VALUES only (or values and formulas, but not
formatting), and then applied new data validation to the new workbook
& replaced the old workbook with the new one.
hope it helps!
susan