The shorter question to spare you from reading more than you need to
I have a few different formulas for use in conditional formatting with custom formula in Google sheets. They are all roughly 1100 characters (yes I know it's unmaintainably long - I don't really have a choice because {gestures vaguely towards management group} circumstances). Is this violating a hard character limit for conditional formatting's custom formula field? I can't find a number anywhere in the documentation or in other questions on here.
If the answer to that is "Yes, this is too long!" Then just say that and all will be solved. If the answer to that is "No, you should be fine" then keep reading.
The long explanation for if the short question's answer is "No you should be fine"
I'm using a google sheet (google spreadsheet? docs spreadsheet? what's the proper vocab here?) to manage a job schedule (yes I know there's more appropriate software to do this but guess whose management team doesn't care!). The sheet lists the status of various print jobs and the day they need to print. I currently have conditional formatting with a custom formula applied to make the text in the row bold if the job is supposed to print tomorrow, today, or was supposed to have printed in the past but hasn't yet. This is to provide a quick visual indication that my department need to start pressing the account managers for more information about any specific job because it's scheduled to print soon. It looks something like
=and({status cell} = {relevant statuses}, today() >= {printdate} - 1)
There are a few extra conditionals in there for if today() is Friday and the job prints on Saturday, Sunday, or Monday since my department does not work on the weekends. It works great. But now management wants me to extend this weekend functionality so that if every day between today() and {printdate} is a day we're closed, bold the text. To solve this, I wrote the following formula:
=and(
not(isblank($H3)),
or(
$A3 = "Approved",
$A3 = "Approved - Template"
),
or(
today() >= $H3 - 1, # Prints tomorrow, today, Or in the past
and( # Prints after 1 day closed
today() = $H3 - 2,
or(
weekday($H3 - 1) = 1,
weekday($H3 - 1) = 7,
countif(range_Holidays, $H3 - 1) = 1
)
),
and( # Prints after 2 days closed
today() = $H3 - 3, # 2 days between now and print
and( # all days in gap are holidays or weekends
or(
weekday($H3 - 2) = 1,
weekday($H3 - 2) = 7,
countif(range_Holidays, $H3 - 2) = 1
),
or(
weekday($H3 - 1) = 1,
weekday($H3 - 1) = 7,
countif(range_Holidays, $H3 - 1) = 1
)
)
),
and( # Prints after 3 days closed
today() = H3 - 4, # 3 days between now and print
and( # all days in gap are holidays or weekends
or(
weekday($H3 - 3) = 1,
weekday($H3 - 3) = 7,
countif(range_Holidays, $H3 - 3) = 1
),
or(
weekday($H3 - 2) = 1,
weekday($H3 - 2) = 7,
countif(range_Holidays, $H3 - 2) = 1
),
or(
weekday($H3 - 1) = 1,
weekday($H3 - 1) = 7,
countif(range_Holidays, $H3 - 1) = 1
)
)
),
and( # Prints after 4 days closed
today() = H3 - 5, # 4 days between now and print
and( # all days in gap are holidays or weekends
or(
weekday($H3 - 4) = 1,
weekday($H3 - 4) = 7,
countif(range_Holidays, $H3 - 4) = 1
),
or(
weekday($H3 - 3) = 1,
weekday($H3 - 3) = 7,
countif(range_Holidays, $H3 - 3) = 1
),
or(
weekday($H3 - 2) = 1,
weekday($H3 - 2) = 7,
countif(range_Holidays, $H3 - 2) = 1
),
or(
weekday($H3 - 1) = 1,
weekday($H3 - 1) = 7,
countif(range_Holidays, $H3 - 1) = 1
)
)
)
)
)
A few notes:
- 'range_Holidays' is a named range containing all non-weekend days that we are closed.
- Column H contains the job's print date
- column A is the job status.
- we are never closed longer than 4 consecutive days.
I can provide a more detailed summary of this formula if you need/want.
Anyway
When brought on to a single line and removing all comments and extraneous whitespace brings this specific formula down to a length of 1068 characters.
When entered into a cell, it returns TRUE and FALSE when I expect it to under all test cases I've thrown at it.
The actual problem
The problem is when I paste the formula into the conditional formatting's formula box - the box turns red and a tooltip saying "Invalid formula" appears. If I remove the leading = sign, the error goes away but the formula isn't applying the formatting where I would expect it to (rows where it returned TRUE when pasted in a normal cell).
If length isn't the issue (which it isn't if you've read this far), the only other thing I could think of is that there's too many nested conditionals - but I'd like to rule that out before I put in the effort for a workaround. Any ideas what might be preventing this from working?
=($A3 = SheetB!$A3)
Instead, you'd have to do=($A3 = indirect("SheetB!"&cell("address", $A3)))
Source: stackoverflow.com/questions/25735025/…INDIRECT
is the workaround when accessing a value from another spreadsheet How to reference data from another sheet in a conditional formula in google docs and Conditional Formatting from another sheet. Would you share a copy of your spreadsheet (excluding any private or confidential information).