smartsheet

How to Show and Hide Checkboxes Based on Row Data in Smartsheet

Summary

I’ve been converting a couple Google Sheets I have been using for my sons ‘bank’ into Smartsheet in order to benefit from Smartsheet Forms (to allow them to enter new transactions into their bank) and … to help me learn/use Smartsheet!

As part of this, I wanted to have a transaction row conditionality show a checkbox on a deposit into their account, this checkbox was a status indicator to myself to move money into their Roth IRA or Brokerage Accounts. This proved to be a little tricky, with a lesson being learned along the way I wanted to share.

Credit to these posts for helping me with key information:
Adding a Checkbox conditionally
Hiding checkboxes

Problem Background

My wife and I had been looking to find a solution for our kids to help them manage money. We had tried a physical bank, but it was just too clunky to go to the bank and access money. We had tried just having money in their wallets that they got for gifts, which was fine but rapidly accumulated during birthdays or holidays.

I opened a UTMA Brokerage account for each of them and also a Roth IRA account for our oldest son who got his first job at McDonald’s and we settled on a 50% savings rate for non-job related income (i.e. gifts) and a 100% savings rate for job related income (i.e. McDonald’s) where they also get an allowance (at least during the Summer months when their friend activities increased and they were looking to go get local pizza).

Sheet Background/Requirements

My Google Sheet had a row for each transaction in their ‘bank’, with a withdrawal and deposit column. Current amount was calculated and I added a checkbox (on DEPOSITS ONLY) to give myself a status indicator to move money into their investment accounts.

example google sheet row for bank spreadsheet
A few rows showing a withdrawal (Fortnite) and a couple deposits (that didn’t qualify for brokerage accounts)

This was working fine but it did require me to do some many copy/pasting for some logic in the forms related to the checkbox.

Smartsheet Solution

Here’s the same rows in Smartsheet

Identical rows in smartsheet (with checkboxes conditionally shown!)

The key is across the 2 posts I referenced in the Summary

  1. Replace the cell contents with a punctuation character and then apply custom formatting to the cell to color the font white which renders it not visible (CANNOT USE COLUMN FORMULA)
  2. Realizing that when a checkbox is checked it wipes the formula for that cell away (this was burning me because of Column Formulas!!)

Replace Cell Contents with Punctuation and Custom Formatting

This was pretty straightforward with a cell formula

=IF([Deposit or Withdrawal]@row <> "Deposit", ",", 0)

The formula checks the value of the dropdown column on the current row and if it’s NOT a Deposit it replaces the cell with a ‘,’. I used ‘not Deposit’ logic so that any blank value here would also substitute the cell contents.

I then adjusted the Custom Formatting for the column

Select a fill for the text format to be White, this means the font will be ‘invisible’ in the cell when it’s a ‘,’.

The ‘Trick”

What burned me was attempting to use a Column Formula for a checkbox. When a checkbox is selected, the cell’s formula is wiped out.

When a checkbox is selected, the cell’s formula is wiped out

Because the column is a Column Formula it’s not possible wipe it out. Smartsheet just avoids processing the box selection. What I did was to create the Column Formula so that I could easily apply the formula to all cells in the column, then convert the cells back to a Cell Formula.

I still have a problem where when I insert new rows Smartsheet knows to pull any Cell Formulas forward to the new row from the previous row (above or below), but since the Cell Formula has been wiped out if the box was checked there’s no formula to pull forward. So if I deposit to their brokerage account as the last transaction I won’t get new rows having this formula copied forward.

More to follow on that problem (maybe)!