Tick checkbox when all cells have been filled (mixed column types)

KGarciaDFKGarciaDF
edited 12/09/19 in Using Smartsheet
12/20/18 Edited 12/09/19

Hello all, 

I need a checkbox to automatically check when all other cells have been filled. I have tried using =IF(ISDATE([Handoff Docs. Received Date]3), 1) = IF(ISTEXT([Engineer Assigned]3), 1) = IF(ISTEXT([Sales Rep]3), 1) = IF([Completed Handoff Doc.]3, 1, 0)

When all columns are text columns this formula works like a charm minus the last part of the formula of course. However, I have all sorts of Column types that I need to make sure are being filled in. Once I insert a different type of column into this formula the checkbox checks and unchecks randomly. How do I accomplish my checkbox automatically being checked when all fields in that same row are not black regardless of column type. 

 

Thank you.

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi,

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, [email protected])

    Have a fantastic day & Happy Holidays!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Have you tried using AND to check for all your different conditions?

    =IF(AND(ISDATE([Handoff Docs. Received Date]3), ISTEXT([Engineer Assigned]3), ISTEXT([Sales Rep]3), [Completed Handoff Doc.]3 = 1), 1, 0)

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You could also try something along the lines of...

     

    =IF(COUNTIFS([Handoff Docs. Received Date]@row:[Completed Handoff Doc.]@row, ISBLANK(@cell)) = 0, 1)

     

    This will look across all of the columns in that row and count how many are blank regardless of column type. If none are blank, then it will check your box. As long as any new columns are added between [Handoff Docs. Received Date] and [Completed Handoff Doc.], you won't even have to update the formula as it is looking across that entire range.

    thinkspi.com

  • This is perfect, Paul. This will definitely help if any updates are needed since it does not require all columns to be listed. Very elegant and simple formula. Thank you! 

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. This came about out of me needing to do this very same thing a few months ago. I started off listing each column , but as columns were added and removed it turned into a real hassle. I figured this HAD to work, so I plugged it in for some testing and it worked like a charm. Glad to see it was of some use to others as well. yes

    thinkspi.com

  • Pam TadsenPam Tadsen ✭✭✭✭✭

    @Paul Newcome

    Thank you so much! I am creating a report that pulls an employee's name only when all cells in a row are checked, and this worked perfectly (after updating the column names, of course). Thank you so much!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Pam Tadsen Happy to help. 👍️

    thinkspi.com

Sign In or Register to comment.