Changing a cell value, in a drop down column

6STEP | Rohann

Hi everyone, can anyone help?

I am trying to change the Status of an activity from the Check box Step completion. I have Sales Step 1-6 in a drop down column, and I have Columns 1-6 as checkboxes. I want to check Step 1 to move the cell value to Step 2, Check step 2 to move the cell value to Step 3 etc.... I am struggling and this is as far as I have gotten:

=IF([Step 1]@row = 0, "Step 1", "Step 2", IF(AND([Step 1]@row = 1, [Step 2]@row = 1), "Step 3"))....

Any ideas guys? Help appreciated!

Paul Newcome

Double check that all of the column names are correct. Are you able to copy/paste the formula directly from the sheet to here?

Heather Duff

Hi @6STEP | Rohann ,

Let's assume that step 2 will never be checked if step 1 isn't also checked, and step 6 will never be checked without step 5 being checked, etc. Try this:

=if([step 1]@row=0,"Step 1",if([step 1]@row=1,"Step 2",if([step 2]@row=1,"Step 3",if([step 3]@row=1,"Step 4",if([step 4]@row=1,"Step 5",if([step 5]@row=1,"Step 6",""))))))

Hope this helps. Let me know if it works!

Best,

Heather

6STEP | Rohann

Hi Heather,

Thanks for getting back to me. I've inserted the formula and encountered the same problem. The formula works for Step 1 and Step 2, but try and trigger Step 3 and it still remains as Step 2. Same for 4, 5, & 6. The formula makes send to me. The result does not. :-(

Paul Newcome

You will need to work backwards. The formula reads from left to right and stops on the first true value.

=IF([Step 6]@row = 1, "Step 6", IF([Step 5]@row = 1, "Step 5", IF([Step 4]@row = 1, "Step 4", IF([Step 3]@row = 1, "Step 3", IF([Step 2]@row = 1, "Step 2", IF([Step 1]@row = 1, "Step 1"))))))

6STEP | Rohann

Hi Paul,

This seems like it should ok, but I get an #UNPARSEABLE....

Paul Newcome

Double check that all of the column names are correct. Are you able to copy/paste the formula directly from the sheet to here?

6STEP | Rohann

Thank Paul, I got it to work. =IF([Step 6 - Quote Prep Quote]@row = 1, "Step 6", IF([Step 5]@row = 1, "Step 5", IF([Step 4]@row = 1, "Step 4", IF([Step 3]@row = 1, "Step 3", IF([Step 2]@row = 1, "Step 2", IF([Step 1]@row = 1, "Step 1"))))))

Paul Newcome

Happy to help. 👍️