Approve All Button

Megan Sultze

I am trying to create a column formula that will change all the rows that have information in it to approved if one box on the top is checked. It is like an "Approve All" button that will let you approve all rows without having to individually check each box when its approved.

I used this formula: =IF($[Approve All]$1 = 1, "Yes", "") but it is an absolute reference and wont let me convert to a column formula.

Let me know what ideas you have, whether it be a formula, automation, etc.

Thank you!

Paul Newcome

You would set the "Approve All" checkbox as a Sheet Summary Field. Then within the column on the sheet itself you can use a column formula referencing the Sheet Summary field.

=[Checkbox Summary Field]#

Paul Newcome

You are going to want to instruct your users to uncheck the box and then save after the old rows get moved.

For the who portion, it sounds like you are using a Modified By column type. Try inserting a text/number column and enter the column formula of

=[Modified By]@row + ""

Now you have static data that should be pulled when the row(s) move.

Paul Newcome

What about using a sheet summary field. Would that be an option?

Megan Sultze

@Paul Newcome How would I do that?

Paul Newcome

You would set the "Approve All" checkbox as a Sheet Summary Field. Then within the column on the sheet itself you can use a column formula referencing the Sheet Summary field.

=[Checkbox Summary Field]#

Megan Sultze

@Paul Newcome Is there a way to automatically uncheck the box once the rows are moved to another sheet?

Paul Newcome

Unfortunately not. To do that we would need a formula in the sheet summary field, but you can only have either a formula OR manual entry. Once you manually check the box, it will override/delete the formula from the field.

Megan Sultze

@Paul Newcome Ok that's what I thought. The problem I'm running in to is that when new entries are created, they are automatically being approved because the box was still checked from the last time. Any other ideas you think would work?

Paul Newcome

What is the trigger for the old rows to be moved?

Megan Sultze

This is the automation I have to move the rows once they are approved. I also have it where it automatically puts the email of who approved it but when it moves to the other sheet, that changes to automation@smartsheet.com. Is there a way to not make it change to that?

Paul Newcome

You are going to want to instruct your users to uncheck the box and then save after the old rows get moved.

For the who portion, it sounds like you are using a Modified By column type. Try inserting a text/number column and enter the column formula of

=[Modified By]@row + ""

Now you have static data that should be pulled when the row(s) move.