Data Validation custom formula length restriction?

The problem isn't the length of the formula, it's that the formula doesn't make sense... it doesn't follow the syntax of an IF-statement...
=IF(condition, true value, false value)
You have the following, which has as it's true value -- F17="" which results in TRUE or FALSE, and as it's False value, the rest of the formuls, which, since it's enclosed in an AND-statement, is also just True or False, which is MAYBE what you want, but unlikely. You can put this formula in a cell, say K17, and use =K17 as the conditional format test. It'd also make it easier to debug!

=IF(ISBLANK(D17),F17="",AND(ISNUMBER(F17),OR(AND(ISNUMBER(B20),ISNUMBER(B21),F17+(G17/12)+(I17/12*0.5)<=B20,F17+(G17/12)-(I17/12*0.5)>=B21),AND(ISNUMBER(B39),ISNUMBER(B40),F17+(G17/12)+(I17/12*0.5)<=B39,F17+(G17/12)-(I17/12*0.5)>=B40),AND(ISNUMBER(B58),ISNUMBER(B59),F17+(G17/12)+(I17/12*0.5)<=B58,F17+(G17/12)-(I17/12*0.5)>=B59),AND(ISNUMBER(B77),ISNUMBER(B78),F17+(G17/12)+(I17/12*0.5)<=B77,F17+(G17/12)-(I17/12*0.5)>=B78),AND(ISNUMBER(B96),ISNUMBER(B97),F17+(G17/12)+(I17/12*0.5)<=B96,F17+(G17/12)-(I17/12*0.5)>=B97))))

The problem is not with the formula itself. I've poured over it multiple times and everything is as I need it to be. The if statement is more complex than the following, but this might give a better idea of what it's checking for:

The problem (in Excel 2000, at least) is that the custom validation formula field restricts/limits you to 255 characters. If you have Excel 2000, check it out for yourself. Here is a string with 255 characters in it, including the '=' sign. Paste it into any custom data validation field and then try to add more characters; it won't allow it.

Thank you for your help BobUmlas, and please keep any ideas of how to get around this shortcoming flowing!
-Brian

I am cross-posting this question on the following forums; FYI
http://www.ozgrid.com/forum/showthread.php?t=84051
http://www.excelforum.com/showthread.php?p=1876449#post1876449

Then as I said, put the formula in cells, and use the data validation to simply reference the cell.

As Bob suggest, you can refer to cells that contain your formula(s). Another approach is to use Named Formula(s) (Insert>Name>Define). Give if a name. and then enter your formula in the "Refers To" field.

lenze

Thanks very much!!! That worked fine!
Does anyone know if newer versions of excel allow more than 255 characters in the custom data validation field? I couldn't find that tidbit of info anywhere on the net, and was just curious...

Sir, is there a work around for this limitation of 256 characters in the custom data validation? I am using office 365 and I worked on building an attendance sheet with all the required validation checks but i still have to include two more conditions and my formula is exceeding the 256 limit. Please help.

Uday2305 said:

is there a work around for this limitation of 256 characters in the custom data validation?

Click to expand...

Not that I'm aware of, as has been said in this thread, you will need to put the formula in a cell

Sir, i am sharing additional context of the work im doing. I have created this attendance tracking file and it already has data validation conditions. Now i have to include a condition to check - if the date is Sunday, if someone applies leave for the days before and after Sunday, then Sunday should allow users to mark as leave and nothing else. I have attached a screenshot of the format of the file. Please help how i can put the formula in a cell for this case.