Conditional formatting formula character limits

[johnboston]

I know this is a simple question but it's aggravating me and though it is probably previously addressed, I'm unable to find it. My question is this ...
In conditional formatting, is there a limit to the number of characters that may be entered in the third line from the right where the first line is "cell value is" and the second line is "not equal to"? I'm trying to type a lengthy formula (>250 characters including spaces and quotation marks, etc.) and at some point the line/box won't allow me to keep typing. I hope that makes sense. Thanks, John

 

[mole999]

250 characters plus sounds a lot of referencing, maybe it can be made smaller, if you post it

 

[johnboston]

Sure, thanks. As background it's a tool that's being used for setting up scheduling on a 24 hour unit. The formula accounts for shift times (for example "730-1600" and length of shift, in this case, 8 hours). Here's what I'm trying to enter. It IS a cumbersome formula, I agree, but in shorter form it has worked. Open to any suggestions ...

=IF(OR(D4="730-1600",D4="1530-2400",D4=800,D4="0800 2330",D4="730-1600 r",D4="1530-2400 r",D4="0800 D"),8,(IF(OR(D4="730-2000",D4="730-2000 r",D4="1130-2400",D4="1130-2400 r",D4="0800 1930"),12,(IF(OR(D4=1930),4,(IF(OR(D4=2330,D4="D 2330",D4="d",D4="dr"),0,"?")))))))

There are spaces (for example in the first occurrence of "0800 2330" there are 10 spaces to account for appearance on the printed document) which, I think, count as characters(?).

Thanks, again for suggestions.

John

 

[mole999]

i guess this is a typo

="1530-2400",D4=800,D4="0800 2330",

numbers or text in the same cell

 

[johnboston]

Yes, the 800 is supposed to be in quotes(?). I started this project months ago and it was recently revisited. It seems though it didn't matter whether the 800 was in quotes or not. I could certainly be mis-remembering on that.

I just checked it again with or without quotes before sending this and it didn't seem to make a difference in the result. It ought to be in quotes, though, is that one suggestion?

I guess, as additional background, what the spreadsheet is being used for is ... people request their shifts from a dropdown menu. For the working model I started out using, for example, the 5 most frequently requested shifts. When the sheet went active and began being used by more individuals the request was made, for example, for the next 5 most frequently requested shifts, as well. Meaning now, for example, I had to include 10 choices in the dropdown and update formulas.

Hope all this makes sense.

Appreciate your thoughts on this.

Thanks.

John

 

[mole999]

i'm not good at those style formulas, some very tuned in people here, know these

 

[MrKowz]

John,

There are ways we can possibly reduce that formula, or at least find a workaround. Can you please post some of your sample data, as well as describe exactly what your criteria is for the conditional format?

 

[mole999]

I'm not sure this is actually CF, just shift patterns to leave a marker, 4, 8, 12 and 0

 

[T. Valko]

I know this is a simple question but it's aggravating me and though it is probably previously addressed, I'm unable to find it. My question is this ...
In conditional formatting, is there a limit to the number of characters that may be entered in the third line from the right where the first line is "cell value is" and the second line is "not equal to"? I'm trying to type a lengthy formula (>250 characters including spaces and quotation marks, etc.) and at some point the line/box won't allow me to keep typing. I hope that makes sense. Thanks, John

Those little boxes are called refedits and yes, they are limited to 255 characters.

 

[T. Valko]

P.S.

It looks like you want to use the "Formula Is" option but you're still limited to 255 characters in the formula.

If you could provide a more detailed explanation of what you want to format we can probalby come up with something.