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

KGarciaDF

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.

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, andree@getdone.se)

Have a fantastic day & Happy Holidays!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Brian W

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 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.

KGarciaDF

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 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. 

Pam 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 Newcome

@Pam Tadsen Happy to help. 👍️