Conditional formatting formula character limits

johnboston

New Member
Joined
Mar 11, 2011
Messages
23
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
250 characters plus sounds a lot of referencing, maybe it can be made smaller, if you post it
 
Upvote 0
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
 
Upvote 0
i guess this is a typo

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

numbers or text in the same cell
 
Upvote 0
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
 
Upvote 0
i'm not good at those style formulas, some very tuned in people here, know these
 
Upvote 0
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?
 
Upvote 0
I'm not sure this is actually CF, just shift patterns to leave a marker, 4, 8, 12 and 0
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top