Select Column Range
I have a form where there are 120 columns and collaborators enter the information through their department dynamic views. For easy math, let's assume each department has 20 columns to fill out. The last column is a checkbox such that if all 120 columns are filled out (essentially not blank), this check box will be marked. I see a couple ways to do this without a helper column:
- Formula along the syntax of =IF((COUNT([Column1]@row, [Column 2]@row, [Column3]@row, … ,[Column 120]@row)/120)=1, 1, 0) ***I ran into the 4K character limit
- Formula along the syntax of IF(AND(ISBLANK([Column1]@row:[Column120]@row), 1, 0) **NOT WORKING. DOES THIS WORK IN SMARTSHEET?
- Automated Workflow so that the conditions are all the columns are not blank but is there a limitation on how many conditions there are?
Best Answers
-
You would use a COUNTIFS across all columns and count how many are blank. If that result is zero, then all cells are filled out.
=IF(COUNTIFS([Column1]@row:[Column120]@row, @cell = "") = 0, 1)
-
You could try an IFERROR.
=IFERROR(original_formula, "")
Answers
-
You would use a COUNTIFS across all columns and count how many are blank. If that result is zero, then all cells are filled out.
=IF(COUNTIFS([Column1]@row:[Column120]@row, @cell = "") = 0, 1)
-
Thank you for the solution Paul. I tried it and it worked. My question is, when the form is not completed, I am receiving the error: "#INVALID DATA TYPE" as columns 1-120 is a mix of text/numbers, drop down menus, and dates. My issue is that this form will be audited and seeing the error may raise flags. Is there a way to keep the checkbox unchecked with using IFERROR() before the IF statement?
-
You could try an IFERROR.
=IFERROR(original_formula, "")
-
Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!