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

Jeff Kline
Jeff Kline ✭✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

Best Answer

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

    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

    image.png
    image.png


    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

    · Share on FacebookShare on Twitter

Answers

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

    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

    image.png
    image.png


    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

    · Share on FacebookShare on Twitter
  • Jeff Kline
    Jeff Kline ✭✭
    https://community.smartsheet.com/discussion/comment/364448#Comment_364448

    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!

    · Share on FacebookShare on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

    · Share on FacebookShare on Twitter
  • Jeff Kline
    Jeff Kline ✭✭
    https://community.smartsheet.com/discussion/comment/364534#Comment_364534

    Thanks Kelly,


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


    Jeff

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!