3

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?

  • Not a solution, but a very interesting thing I found when building a workaround: Google sheets does not support indirect references for the conditional formatting field. Like, if you want to equate a cell on sheet a to a cell on sheet b, you can't do a conditional of =($A3 = SheetB!$A3) Instead, you'd have to do =($A3 = indirect("SheetB!"&cell("address", $A3))) Source: stackoverflow.com/questions/25735025/… – superluminal Nov 27 at 17:21
  • 2
    Not a solution per se - have you considered breaking it down with some helper columns. Your conditional format might rely on the value in, say, just the last helper column rather than the convoluted mega-formula. – Tedinoz Nov 28 at 10:16
  • That's the workaround I was working on when I stumbled on the tidbit above. It's what I'm probably going to end up going with, but I'm still curious if anyone can shine some insight on why it's not working directly inside the conditional format panel – superluminal Nov 29 at 22:04
  • That's ironic because 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). – Tedinoz Dec 3 at 1:10
  • We're using google apps for business and I apparently can't share the sheet outside the organization without violating some security policy. No matter, the sheet is working as I expect it to. – superluminal 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.