Status Change Formula Unparseable

GSA Rob

My sheet requests dates at certain milestones which should change a status from 1-10. My formula is not working and I think it is likely a syntax problem. Can someone assist please?

=IF([10. Date of Closeout (Kayla)]@row,"10",IF([9. Date Invoice Received (PM)]@row,"9",IF([8. Date RR is Received (PM)]@row,"8",IF([7. Delivery Date/ POP End Date (David/Kayla)]@row,"7",IF([6. Date Awarded]@row,"6",IF([5. Date Acquisition Package Submitted (PM)]@row,"5",IF([4. Date PR Approved (Kayla)]@row,"4", IF([3. RWA Acceptance Date (PM/Kayla)]@row,"3",IF([2. IGE Entered in RETA (PM)]@row,"2",IF([1. Project Assigned to PM (Kayla)]@row,"1",))))))))))

Danielle Arteaga

There are a few issues with the syntax for this formula, and it's not clear how to fix it. Could you describe, just in plain language, what you'd like to have happen and in what column?. Like this:

If [10. Date of Closeout (Kayla)]@row is ______, then Status@row="10". Else, if [9. Date Invoice Received (PM)]@row is _____, then Status@row="9". Etc.

Also, if you can share a screen cap of your sheet, that will make it much easier to give you sound guidance.

Also, FWIW, if it is at all possible, I recommend removing any punctuation marks from your column names. If you build a lot of nested formulas, having things like periods, commas, braces, or parentheses in your column names makes spotting mistakes in a formula a brutal task. You might explore using super simple column names then leveraging the description field for column headers (find in Column Properties) if more detail or explanation is needed.

GSA Rob

Below is the screen shot of the sheet. Once a date is input in "1. Project assigned to PM (Kayla)", and then "2 IGE...", and so on, the status column should update 1-10. We use the status number to identify where in the process it is and who is responsible for the next step. A form is used to input the project initially which populates the date in "1. Project Assigned..." and then the status should be a 1. Once a date is input in column "2. IGE..." then the status should change to 2. We've used this same format in other applications but our agency does not support those applications so we are transitioning an existing program over to SmartSheet. This is the only function in our sheet besides the color codes.

I will try and clean up the column names in the future to avoid function issues.

ccarriger

My two cents--I'd build 10 automations, one for each status, and use that instead of a formula. Since you have clear "when this happens" steps, an automation will be better in my opinion.

GSA Rob

Ive created a workflow and that status column is not updating. I must be doing something wrong here.

Danielle Arteaga

@ccarriger probably has the right idea here given the complexity of what you want to achieve. But, whether you do this as an automated workflow or a formula, the conditions are challenging, because what you want is a rolling status.

(FWIW - It may be that your workflow didn't record a value because you created the workflow AFTER you already had data in the relevant cells on your sheet.)

Assuming you want to go the formula route, here's what I worked out for you. NOTE: I don't know the names of your columns 1-10, so I'm going to assign them generic names - you will need to replace with the correct ones.

Put this formula in your Status column and set it as a column-level formula:

=IF(NOT(ISBLANK([10. Tenth Event]@row)), "10", IF(NOT(ISBLANK([9. Ninth Event]@row)), "9", IF(NOT(ISBLANK([8. Eighth Event]@row)), "8", IF(NOT(ISBLANK([7. Seventh Event]@row)), "7", IF(NOT(ISBLANK([6. Sixth Event]@row)), "6", IF(NOT(ISBLANK([5. Fifth Event]@row)), "5", IF(NOT(ISBLANK([4. Fourth Event]@row)), "4", IF(NOT(ISBLANK([3. Third Event]@row)), "3", IF(NOT(ISBLANK([2. Second Event]@row)), "2", IF(NOT(ISBLANK([1. First Event]@row)), "1", ""))))))))))

Here's a screen cap of the test I did to ensure this works for you:

Hope this helps.

GSA Rob

This worked! Thank you so much for your time!!!