Formula for writing review status updates

Rachael Stammers

OK, so this seems like it's going to be a long shot... hopefully do-able though!

I currently have 4 Sheets as follows:

1. Safety Report - this is where employees submit a form containing information about a hazard, near miss or incident.
2. Safety Review - this sheet is where the safety manager starts their initial review of the hazard/near miss/incident
3. Safety Investigation - this is where the safety manager adds information about their investigation
4. Safety Assurance - where the safety manager tracks the assurance activities and effectiveness of corrective actions, and then a checkbox for when the safety assurance is complete and the investigation closed

In sheet 2 I have a review status column, where the safety manager updates the status according to what phase they are working on WRT that safety report. The safety managers have identified that they don't want to have to keep going back to sheet 2 to update this status. So, I have added a checkbox column in sheet 2, 3 and 4, where they check the box when they move on to that phase. From that checkbox I want to write a formula that changes the status in the Review Status column.

The of the different status are:

• Initial Safety Review
• Investigation
• Corrective Action Implementation
• Assurance Review
• Approval Request
• Closed

So, I see the formula saying something like

=IF(Sheet 2 Safety Review is Checked / Yes, then 'Sheet 2 Review Status is "Initial Safety Review"), IF(Sheet 3 Safety Investigation is Checked / Yes, then 'Sheet 2 Review Status is "Investigation"), IF(Sheet 3 Corrective Actions Identified is Checked / Yes, then Sheet 2 Review Status is "Corrective Action Implementation"), IF(Sheet 4 Assurance Review is Checked / yes, then Sheet 2 Review Status is "Assurance Review"), IF(Sheet 4 Approval Request is Checked / Yes, then Sheet 2 Review Status is "Approval Request"), IF(Sheet 4 Closed is Checked / Yes, then Sheet 2 Review Status is "Closed")

Sheet 2 Checkbox for the Safety Review

Sheet 2 Review Status that I'd like to be automatically updated according to when checkboxes are updated

Jason Tarpinian

It looks like you'd want to reverse your logic, as your IF function will end after the first TRUE statement. So your status, as you have written, would never get past "Initial Safety Review" (unless your team unchecked the previous sheet's boxes too as they moved along). And would it be safe to assume each sheet has multiple incidents on it? In this case, you'll need to use a unique ID value to check against the proper check box, for my formula below, I'm using "Incident ID", it's just a value that needs to be unique across all of your sheets.

I built my sample on a single sheet for simplicity, but this formula would be on your sheet 2 and all of the ranges in the formula would be cross-sheet references to the other sheets (something more like below). Then just repeat the part of the formula in bold as many times as you need until you get back to "Initial Safety Review".

=IF(INDEX({Sheet 4 Closed}, MATCH([Incident ID]@row, {Sheet 4 Incident ID}, 0)) = 1, "Closed", IF(INDEX({Sheet 4 Approval}, MATCH([Incident ID]@row, {Sheet 4 Incident ID}, 0)) = 1, "Approval",... REPEAT AS NEEDED

Having said that, would it be a possible solution to keep this all on one sheet, and use reports/filters to push it to the next "sheet"? That way all of your data could stay in one nice manageable row, instead of having it across 4 different sheets?

Brent Wilson

@Rachael Stammers Yes, that is very possible..

It is also a two-part answer with (A) The first part being about getting the data from those sheets in a usable format and (B) once you have that data create an IF statement to evaluate for the Solution

and as with everything, there are a few ways to do it.

1. Smartsheet uses Named 'References' unlike something like excel where you can say "Sheet2" you will have to define that in the formula.
   1. As you write the formula you will see the ability to "Reference Another Sheet" in the formula. This is where you would want to create your links to the other sheet.
2. Another method that will add extra columns to your second sheet would be to create helper columns showing the status of each of the checkboxes on the other sheets. You would perform a VLOOKUP for this method to get the status of the checkboxes on the other sheets.

I prefer the second method.. and it also might be a way to get it working and then you can try your expertise at writing it in method 1.

You will also need a way to "link the records" like a ticket number for both to tell smartsheet it is the same incident.

If you could provide some screen captures with fake data I might be able to assist in writing the statement for you.

Using Method 2 would result in something like this in the Review Status Column

=IF([Sheet4CheckClosed]@row=1, "Closed", IF([Sheet4CheckAppvlReq]@row=1, "Approval Request", IF([Sheet4CheckAssuranceRev]@row=1, "Assurance Review", IF([Sheet3CheckCorrAct]@row=1, "Corrective Action Implementation", IF([Sheet3CheckSafeInv]@row=1, "Investigation", IF([Safety Review]@row=1, "Initial Safety Review", "Not Started"))))))

Notice how I started at the end with Closed and worked backward as if you start at the beginning say Initial Safety Review the IF statement will always evaluate True and never return Closed as the Initial Safety Review will always be checked. I also included a Case if the Initial Safety Review has not been checked.

I say Nested IF statements are like cutting a block of cheese and as you rule out a logic case it gets removed.

You can see how it will get more complex if you start doing links for each of the sheets in the IF statement.. that's why I prefer the helper columns.. You can also hide them once created so other users do not see them.

Feel free to reach out if you need any more guidance

Rachael Stammers

Thank you @Jason Tarpinian and @Brent Wilson I really appreciate your help with this.

What I've done in a hope that it makes it easier and I won't need to Match / Link the records across sheets, is created columns in Sheet 2 that show "Yes" if the box is checked and "No" if it is not. Does that make it any easier?

This is the column I want update

based on these answers

Rachael Stammers

@Jason Tarpinian and @Brent Wilson

I actually changed all of the column info, to have the Yes / No columns in the sheet they refer to, for example, if the checkbox is in sheet 3, I have a column in sheet 3 that asks if checked then Yes, otherwise No). Then in sheet 2, I used this formula =INDEX(Sheet x column YY, MATCH Unique ID NO with Unique ID Number, 0)). This works perfectly. However, if the row information hasn't yet been added to Sheet 2, it shows as #NO MATCH.

For the Safety Review Started Column, I used the formula =IF([Check when review starts]@row = 1, "Yes", "No") because it is a checkbox in that same sheet.

I then wrote the formula below in the Review Status column, and it works for all except Safety Review Started. It's not capturing that it's a "Yes"

=IF([ASR Closed]@row = "Yes", "Closed", IF([Approval Request Started ]@row = "Yes", "Approval Request", IF([Assurance Review Started]@row = "Yes", "Assurance Review", IF([Corrective Action Implementation Started ]@row = "Yes", "Corrective Action Implementation", IF([Safety Inspection Started? ]@row = "Yes", "Safety Inspection", IF([Safety Review Started?]@row = "Yes", "Safety Review"))))))

Brent Wilson

Rachael Stammers

It is the #NO MATCH in the other columns causing the problem. You can see it working on the 2nd to last selection that shows Safety Review.

I would look at adding ISERROR or IFERROR functions in those columns..

My assumption is they are blank and as a result, it doesn't know what to do.

You could have it put in a No if it returns a #NOMATCH

Let me know how it goes

Rachael Stammers

Hi @Brent Wilson

Firstly, I apologize for the delay in responding.

The reason I'm getting the #NOMATCH is because it's tied to the Unique ID #, and if that that Unique ID # has not been added to the sheet (3 or 4) that the formula is looking at. To prevent it from occurring, as soon as we have a report submitted, I've been going into each sheet and adding a row with the same Unique ID #.

As a reminder, I have 4 sheets:

Sheet 1 -No data from this sheet is used in the Status Update formula

Sheet 2 - This is the sheet where the formula resides.

In sheets 3 and 4 I capture Yes / No answers relating to whether a checkbox is checked or unchecked. Then in Sheet 2 I import that information using the following formula

=INDEX({3. Safety Investigation Range 5}, MATCH([Safety Report ID #]@row, {3. Safety Investigation Range 4}, 0))

The information presented in the cells from the above formula is what is creating the #NOMATCH in the status column.

The formula I am using for the Status Update is

=IF([ASR Closed]@row = "Yes", "Closed", IF([Approval Request Started ]@row = "Yes", "Approval Request", IF([Assurance Review Started]@row = "Yes", "Assurance Review", IF([Corrective Action Implementation Started ]@row = "Yes", "Corrective Action Implementation", IF([Safety Investigation Started?]@row = "Yes", "Investigation", IF([Safety Review Started?]@row = "Yes", "Initial Safety Review"))))))

I think the issue is in the Yes / No formula. Rather than allowing it to say #NOMATCH, I should add a statement to say "If not found, leave blank"

Brent Wilson

@Rachael Stammers

Simply wrapping it all in an IFERROR should help

IFERROR( value value_if_error )

So

=IFERROR(IF([ASR Closed]@row = "Yes", "Closed", IF([Approval Request Started ]@row = "Yes", "Approval Request", IF([Assurance Review Started]@row = "Yes", "Assurance Review", IF([Corrective Action Implementation Started ]@row = "Yes", "Corrective Action Implementation", IF([Safety Investigation Started?]@row = "Yes", "Investigation", IF([Safety Review Started?]@row = "Yes", "Initial Safety Review")))))) ,"")

In summary, if it returns and #NOMATCH then put "" (blank) otherwise evaluate the formula