Using Checkbox to Set the value of a second checkbox in same sheet

Options

I have searched for awhile and have not found a solution.

I have two columns. [Waitlist] and [Waiver Granted]. Both use Checkboxes.

For this subset of data, [Waitlist] is generally true (checked) for each row.

I want the user to be able to check the checkbox in the [Waiver Granted] column in one row and have it automatically uncheck the [Waitlist] checkbox for the same row.

I would prefer for the formula to be in the [Waiver Granted] column since its a relatively rare change. Finally, the data is likely being displayed for the user in a Report, is there a way to force the report to update after the box is checked?

Thanks

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Jeff Kline

    You can do this with automation within your sheet. It is very straight forward to build. You cannot do this with a formula if you also want users to manual update the same checkbox column. You can't have formulas and manual entries happening in the same cell.

    To build the automation, open your Automation


    And since Reports are dynamically linked to the data, any change will automatically be updated in your report, assuming your checkbox columns are in the report.

    Will this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Jeff Kline

    You can do this with automation within your sheet. It is very straight forward to build. You cannot do this with a formula if you also want users to manual update the same checkbox column. You can't have formulas and manual entries happening in the same cell.

    To build the automation, open your Automation


    And since Reports are dynamically linked to the data, any change will automatically be updated in your report, assuming your checkbox columns are in the report.

    Will this work for you?

    Kelly

  • Jeff Kline
    Options

    Thanks Kelly, That worked. I was also able to use that same idea to create the reverse workflow and a few others of a similar vein.

    Is there a way to get an automation to automatically update (refresh) the Sheet or Report that is showing? When i check these associated checkboxes, it does not immediately refresh and uncheck (or check) the other ones. I can do a manual refresh (or save) and that will work, but I'd like my users to see immediate changes.

    Thanks!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Jeff Kline

    Automation requires the sheet to 'register', in effect, the trigger. This happens when the sheet is saved or refreshed. This must always occur for the automation to execute.

    The only way to make it happen immediately is by formula, which would require a helper checkbox column. Via a formula, this checkbox column would mirror your Waitlist column unless there was an entry in your [Waiver Granted] column. Then the Waiver Granted would force it to be unchecked. This new helper column would become the Waitlist column that would indicate the real status of Waitlist.

    Would you like to proceed that route? A helper column is the only way, via formula, to allow manual entry in a column yet have a formula in other situations.

    Kelly

  • Jeff Kline
    Options

    Thanks Kelly,


    The solution you offered worked. The users will have to get used to saving and refreshing to see changes sometimes.


    Jeff

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!