Trying to checkmark an entire column based on an absolute cell reference. Help pretty please.
Hey there,
So, I'm trying to automate a process where if a new row comes in (through a form) with a checkbox "checked" (in column Open ECO? row 1 only) that the check marks in Column 18 will all be checked.
Any thoughts?
thanks in advance,
ryan
Best Answer

So I took what Andrée suggested and modified it a tad to work for how I needed.
I changed my "Open ECO?" column to text/numbers instead of a checkbox
When new rows came in that cell was automatically assigned a 1 value
My Sheet Summary now looked like =SUM([ECO?]:[ECO?])
And my "Column 18" fx was =[Field 2}#
End result, was that if there was a 1 value in "Open ECO?" the check boxes in "Column 18" would all be checked.
Crude but working
thanks again for the help,
ryan
Answers

I hope you're well and safe!
You can add a formula/link to that cell in the Sheet Summary and then reference that in Column18 and convert it to a column formula.
Make sense?
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
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 for help with Smartsheet, integrations, general workflow advice, or anything else.

Hey Andrée,
I'm new to Smartsheet and not familiar with using the sheet summary. When I open a new "field" in sheet summary I selected "text/number". I tried using a formula =[Open ECO?]$1 but I get an #INVALID COLUMN VALUE.
Any thoughts? I'm I doing this wrong?
I thought column formulas couldn't have absolute cell values in them
thanks,
ryan

Great! So close.
So I did some more testing and got this to work.
=IF([Open ECO?]$1, 1) in "Field 2"
Then I applied a column formula in "Column 18" as =[Field 2]#
However when I add a new row the Field 2 fx changes to =IF([Open ECO?]$2, 1) and no longer works.
Any idea how I get my field fx to keep the Open ECO? at $1 and not change to $2?

So I took what Andrée suggested and modified it a tad to work for how I needed.
I changed my "Open ECO?" column to text/numbers instead of a checkbox
When new rows came in that cell was automatically assigned a 1 value
My Sheet Summary now looked like =SUM([ECO?]:[ECO?])
And my "Column 18" fx was =[Field 2}#
End result, was that if there was a 1 value in "Open ECO?" the check boxes in "Column 18" would all be checked.
Crude but working
thanks again for the help,
ryan

Excellent!
You're more than welcome!
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
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 for help with Smartsheet, integrations, general workflow advice, or anything else.

I tried the Best Answer and it did not work. Sum of the Column is not 1 therefore it does not check off a checkbox. As well are the Best Answers vetted? I see that there is a typo in the formula for the Field 2 opening with a [ and closing with a }

I have worked with what I could read in this thread and come up with this.
If you wish to check off an entire column all at once within a grid once a form has been completed.
I accomplished it following these steps
Add a Trigger Column with a default answer of 1 (it can be hidden)
In the Sheet Summary of your Grid add a Field in this case the author called it Field 2.
In Field 2 the formula should be =SUM([Trigger Column]:[Trigger Column])
In an adjacent column with checkboxes input a formula to check of the boxes once Field 2 adds up to the total number of questions or rows you wish to check off all the boxes at once.
That formula is =IF([Field 2]# = 14, 1, 0) Note 14 is the count I required but can be any count that you need. Just change it.
I wanted a delayed action to my automation so I had a question that purposely did automate a 1.
Then I added a separate column with a Approval Submit Checkbox highlighted in red. When the user checks the box I have it automated to drop a 1 in the remaining cell which then triggers the entire event adding up to the total of 14 (my requirement) and then clearing the form and moving the rows to an archive once it has been approved.
I hope this helps someone else.
Help Article Resources
Categories
Check out the Formula Handbook template!