Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
Hello!
So I have 2 checkbox columns, if the second one is checked the first one shouldn't be checked anymore, I tried to add an IF formula to the column specifying that argument but it didn't work.
How do the boxes get checked? If they are checked manually, it will delete any formula that is in the cell.
Yes exactly, they're checked manually, so I think there's no other solution. the problem is that if the user forgets to uncheck the first one after checking the second, it will mess up the calculation!
You could add in a helper Text type column and enter the following...
=IF(COUNTIFS([First Check Column]@row:[Second Check Column]@row, 1) = 2, "Must uncheck first box if second box is checked!", "")
This will show a blank column next to the checkboxes, but if both boxes are checked, the text within the quotes will appear. You can then couple that with conditional formatting to turn the helper cell Red and Bold and the box that needs unchecked red.
Once the box is unchecked, everything will go back to normal.
Paul,
Great workaround!
Best,
Andrée
Hi Paul,
Thank you so much, this is a great workaround indeed.
It works perfectly! Although the message says just "Uncheck [Column]" to limit the space taken.
Thanks. I have used this and other variations of it quite a bit. My personal favorite is when I have a series of checkboxes. I'll use an INDEX/COUNTIFS combo to pull text from the first row based on how many boxes they have checked. Saves from having a huge nested IF with a bunch of quotes when you have 10 or 15 steps that just need checked off.
Happy to help.
And no worries on the space thing. My personal preference is something a little longer. I'll then use the wrap text feature. Between the conditional formatting bringing bold text and color to the cell, the wrapped text also makes the row much taller. Just one more way to bring attention to it. Haha
Yes, the INDEX/COUNTIFS is
What's really fun is an INDEX/MATCH/MATCH where you have an additional INDEX/MATCH used to pull the data for the first MATCH and another INDEX/MATCH used to pull the data for the second MATCH.
(I'm pretty sure I short circuited a few brain cells working on that particular beast. Hahaha)
I'm sure you did! I did it just by reading it. Interesting!
I like to be able to dragfill, so I try to write formulas that are as flexible as possible. When you're comparing tables to other tables on different sheets to pull specific data, you have to get a little creative to keep from having to edit a bunch of individual formulas.
On the bright side, I have certainly learned a lot. I do remember how many times you've helped me get started too. Between you, Craig, and Mike, there is A TON of knowledge to be found here in the community. It's great!
Yes, I also always try to set it up as "future proof" as possible.
Yes, the Smartsheet Community and the knowledge and willingness to help and share is by far the best I've been a part of professionally.
Hi all, I am trying to build out a formula that will return "True" if the CIF #@row can be found above it in the same column (CIF #) AND the Days Since Created is between 30 - 365 days. All of the formulas I have tried have only gotten me so far as the "AND". Adding in the dates screws it up. The goal is to determine if…
I am getting a lot of REF errors for my predecessors and I can't figure out why. At first I thought I might have REF for predecessors to predecessors but that's not the case. When I export the file into Excel it shows the IDs in the place where it shows REF in SS. Each time I refresh the file I feel like different values…
I am trying to create a sheet summary, where the field is a formula that relies on a contact card within a column. However, it keeps returning a result of "#NO MATCH". The columns involved are, 1:) Priority (symbol column) for Up, Unchanged, and Down, and 2:) Contact Card column. I am trying to get a sheet summary formula…
