Trying to checkmark an entire column based on an absolute cell reference. Help pretty please.

ryan harrison · 06/17/21

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

ryan harrison · 06/17/21

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

Andrée Starå · 06/17/21

Hi @ryan harrison

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!

ryan harrison · 06/17/21

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

ryan harrison · 06/17/21

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?

ryan harrison · 06/17/21

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

Andrée Starå · 06/17/21

@ryan harrison

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!

Kim Ryan · 12/19/22

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 }

Kim Ryan · 12/19/22

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.

Trying to checkmark an entire column based on an absolute cell reference. Help pretty please.

Best Answer

Answers

Help Article Resources

Categories