If statements

sdolan

I'd like to create an if statement to automatically check a box if a status column has any of the 6 drop down options selected.

This is the first step in an exercise to try to tackle parent/child columns in reporting but need to crack this step first. Thanks in advance

KPH

Question 1

Please can you write the first OR statement so I know where all the brackets need to go.

The OR statement is this part

OR([Drop down]@row = "Thing 1", [Drop down]@row = "Thing 2", [Drop down]@row = "Thing 3", [Drop down]@row = "Thing 4", [Drop down]@row = "Thing 5", [Drop down]@row = "Thing 6")

In my case the column name is Drop down. If yours is called Status you need to replace [Drop down] with [Status] (the square brackets will be removed as your column name does not have a space in it).

So it would look like this

OR(Status@row = "Thing 1", Status@row = "Thing 2", Status@row = "Thing 3", Status@row = "Thing 4", Status@row = "Thing 5", Status@row = "Thing 6")

Then you replace "Thing 1" etc with whatever is in your list, retaining the quote marks.

Question 2

Also, I don't seem to be able to add the formula to a checkbox without overwriting the checkbox. Any guesses what I am doing wrong?

The checkbox column should be created as Checkbox type

This just means the 1s and 0s in that column are displayed as ticked or unticked boxes.

Your formula will output 1s and 0s. These will be displayed as ticked or unticked boxes.

What do you mean by "overwriting the checkbox"? What do you have in those cells at the moment?

Question 3

The Column title I am trying to read is called Status. This is what I have so far:

=IF(STATUSISBLANK([Drop down]@row), 0, 1)

The column title is the part in the square brackets - in my example the column title was Drop down, so you replace Drop down with Status if you column is called Status

=IF(ISBLANK([Status]@row), 0, 1)

(again the square brackets are not needed as your column heading does not include a space - you can leave them out or Smartsheet will remove them)

KPH

Are there only 6 possible options and blank in the drop down?

If so, you check use an IF function combined with ISBLANK to return 0 in a check box formatted column, if the column with the drop-down is blank and 1 if not blank. The 1 will check the box.

The formula is

=IF(ISBLANK([Drop down]@row), 0, 1)

The result is

If there is a 7th option in the drop down, then you will need to use an OR within the IF, to check the box only if one of the 6 values is selected.

That formula is

=IF(OR([Drop down]@row = "Thing 1", [Drop down]@row = "Thing 2", [Drop down]@row = "Thing 3", [Drop down]@row = "Thing 4", [Drop down]@row = "Thing 5", [Drop down]@row = "Thing 6"), 1, 0)

The result is

Does that help? I hope so.

sdolan

This is really helpful. Thanks.

Please can you write the first OR statement so I know where all the brackets need to go.

Also, I don't seem to be able to add the formula to a checkbox without overwriting the checkbox. Any guesses what I am doing wrong?

Thanks

sdolan

The Column title I am trying to read is called Status. This is what I have so far:

=IF(STATUSISBLANK([Drop down]@row), 0, 1)

KPH

Question 1

Please can you write the first OR statement so I know where all the brackets need to go.

The OR statement is this part

OR([Drop down]@row = "Thing 1", [Drop down]@row = "Thing 2", [Drop down]@row = "Thing 3", [Drop down]@row = "Thing 4", [Drop down]@row = "Thing 5", [Drop down]@row = "Thing 6")

In my case the column name is Drop down. If yours is called Status you need to replace [Drop down] with [Status] (the square brackets will be removed as your column name does not have a space in it).

So it would look like this

OR(Status@row = "Thing 1", Status@row = "Thing 2", Status@row = "Thing 3", Status@row = "Thing 4", Status@row = "Thing 5", Status@row = "Thing 6")

Then you replace "Thing 1" etc with whatever is in your list, retaining the quote marks.

Question 2

Also, I don't seem to be able to add the formula to a checkbox without overwriting the checkbox. Any guesses what I am doing wrong?

The checkbox column should be created as Checkbox type

This just means the 1s and 0s in that column are displayed as ticked or unticked boxes.

Your formula will output 1s and 0s. These will be displayed as ticked or unticked boxes.

What do you mean by "overwriting the checkbox"? What do you have in those cells at the moment?

Question 3

The Column title I am trying to read is called Status. This is what I have so far:

=IF(STATUSISBLANK([Drop down]@row), 0, 1)

The column title is the part in the square brackets - in my example the column title was Drop down, so you replace Drop down with Status if you column is called Status

=IF(ISBLANK([Status]@row), 0, 1)

(again the square brackets are not needed as your column heading does not include a space - you can leave them out or Smartsheet will remove them)

sdolan

Doh, Thanks. Cracked it. Have another question in a new threat on parent/child relationships in reports if you happen to know anything about that?

Thanks so much

Suzanne

KPH

Great news. Glad that I could help. I have found your other question and added a comment. Hope it helps.