Project Management - IF(OR(AND())) - Formula Help

Jacob Laan

I've been working on this for some time and just  can't seem to get it to work the way I need it to. I am attempting to build one sheet that will allow me to track the status of all the projects in my section, while also tracking the progress of tasks within each project, for both current and historical projects.

I have parent project rows for each project with child task rows and I've created both project and task status columns, in addition to three date columns (Due | Status | Fulfillment). I'm trying to use this combination of columns to do a couple things:

• Conditionally color project rows by status and date (working)
• Conditionally color task rows by status and date (working)
• Notify team members when task/projects are updated that require their attention (working)
• Get a snapshot of both the current status of projects as well as a snapshot of our historical completion of tasks and projects as that relates to their associated timelines (i.e., looking back over X period of times, these are the projects/tasks we completed on time, these are the ones we were late on, etc.) (close, but no cigar)
  • One additional piece of context on this piece - I am currently trying to accomplish this using an RYGB ball "Overdue" column where:
    • Red represents project or task columns that are currently overdue;
    • Yellow represents projects or tasks that are either currently beyond their safe date but before their due date or projects/tasks that were completed historically beyond their due date;
    • Blue represents projects/tasks that are in progress and not beyond their safe date; and
    • Green represents projects/tasks that are complete and were completed before their due date.  

It seems straight forward enough, but my formula has become so verbose that I'm having trouble following it any more. I'm getting some rows where the formula is resolved and I get a red ball, but the rest are returning an "INVALID OPERATION" error. The formula current looks like this:

=IF(

    OR(

        AND(

            [Task Status]1<>"",

            [Task Status]1<>"Complete",

            [Task Status]1<>"Requested",

            [Task Status]1<>"Reviewed - Approved",

            [Task Status]1<>"Reviewed - Revise",

            [Due Date]1<TODAY()

                    ),

        AND(

            [Project Status]1<>"",

            OR(

                [Project Status]1="Pending Assignment",

                [Project Status]1="In Progress",

                [Project Status]1="Overdue"

                )

                )

            ), 

    "Red", 

    IF(

        OR(

            AND(

                TODAY() > [Safe Date]1,

                TODAY() <=[Due Date]1,

                [Project Status]1<> "",

                [Project Status]1<> "Completed",

                [Project Status]1<>= "Delivered"

                ),

            AND(

                [Safe Date]1< TODAY(),

                TODAY() <=[Due Date]1,

                [Task Status]1<> "",

                [Task Status]1<>"Complete",

                [Task Status]1<>"Requested",

                [Task Status]1<>"Reviewed - Approved",

                [Task Status]1<>"Reviewed - Revise"

                )    

            ),            

    "Yellow",

    IF(

        OR(

            AND(

                [Status Date]1>[Due Date]1, 

                [Task Status]1 <> "",

                [Task Status]1="Complete",

                [Task Status]1="Requested",

                [Task Status]1="Reviewed - Approved",

                [Task Status]1="Reviewed - Revise"

                ),

            [Fulfillment Date]1>[Due Date]1, 

            AND(

                [Due Date]1 < TODAY(), 

                [Project Status]1<>"",

                [Project Status]1<>"Completed",

                [Project Status]1<>"Delivered"

                )

            ),    

    "Green",

    "Blue"

        )

    )

)

So...couple of questions to sum this rant up:

1) Is there a function similar to "IN" or "INLIST" used in many other languages that allows the formula to check a list?

2) I've been writing in Npp and pasting into Smartsheet because it's easier for me to format the code in a readable way in Npp, but when i past back into Smartsheet, I get an error that seems to be produced by spacing before any opening parentheses. This requires an additional step in Npp wherein I have to first remove all unnecessary spaces and then collapse the code down to one 'string' (for lack of a better term). Any work around for this?

3) How does Smartsheet resolve 'null' values in formulas? For example, I've seen on occasion that if a formula looks a date column but no date is entered, I will get an error until I specifically go to the cell and select 'None' in the date picker.

4) What's wrong with my formula?

5) Am I trying to do too much with one sheet? Would it be better to separate the current project/task status monitoring function from the historical tracking function? Alternative solutions?

 

I appreciate any help or pro-tips the community might have to offer as they apply to both this particular formula, but also project management through Smartsheet generally.

 

Thank you!

Jacob Laan

Just identified the source of the 'INVALID OPERATION' error:

 [Project Status]1<>= "Delivered"

Remaining questions still stand.

Mike Wilday

Try removing the equal sign. <> means does not equal.