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?

8
  • 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/… Nov 27, 2019 at 17:21
  • 3
    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, 2019 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 Nov 29, 2019 at 22:04
  • 1
    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, 2019 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. Dec 3, 2019 at 16:43

2 Answers 2

0

Maybe not the perfect answer, but have you considered setting up multiple conditional formatting rules each that apply the same formatting, but for the more granular conditions. For example, a rule for ' # Prints after 1 day closed', etc.

0

Regarding the character limit for a custom formula, it might be the same as the cell content which is 50,000 (fifty thousand) characters. As the space to write/read a custom formula is very limited I suggest that instead of using a single rule with a long custom formula use multiple rules or use Google Apps Script to edit the single formula. Another advantage of using Google Apps Script is that you could keep the comments by following JavaScript syntax.

Regarding the error, custom formulas can't use references or named ranges that points to another sheet. The workaround is to use INDIRECT (like INDIRECT("Sheet 1!A1").

Related

2
  • The formula was significantly less than 50,000 characters in length so I don't think that's the limit that is being applied to the custom formula entry. I did end up using indirect references in the solution that I had eventually arrived at, which I detailed in the comment I made on the question. May 14, 2020 at 18:37
  • Did you know that you can answer your own question? Ref. webapps.stackexchange.com/help/self-answer
    – Rubén
    May 14, 2020 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.