Best Of

To achieve this, you can set up where the status of equipment in the original sheet is automatically updated when a corresponding entry is submitted in the second sheet (the removal log). Since you're using a unique identifier for each piece of equipment, that makes this process much easier.

1: Ensure Both Sheets Have the Unique Identifier

• Original Equipment Sheet: Include a column like Equipment ID.
• Removal Log Sheet: Include the same Equipment ID column submitted via the form.

2: Use a Cross-Sheet Formula to Update Status

In the Original Equipment Sheet, add a column called Status (or similar), and use a cross-sheet formula to check if the equipment ID exists in the Removal Log.

Example (in the Status column of the Original Sheet):

=IF(COUNTIF({Removal Log Equipment ID}, [Equipment ID]@row) > 0, "Removed", "Active")

• {Removal Log Equipment ID} is a cross-sheet reference to the Equipment ID column in the Removal Log Sheet.
• [Equipment ID]@row refers to the current row’s equipment ID in the Original Sheet.

This formula checks if the equipment ID exists in the removal log. If it does, it marks the status as "Removed", otherwise "Active".

3: Set Up the Cross-Sheet Reference

To create {Removal Log Equipment ID}:

Click into the formula cell.

When prompted to select a range, choose the Equipment ID column in the Removal Log Sheet.

Name the reference something like Removal Log Equipment ID.

If you want then you can also pull in other data from the Removal Log (like removal date, reason, etc.) using INDEX(MATCH(...)) formulas.

I know this was a little long but hope it helps!

Hello,

I'm kind of confuse, because on your provided Screenshot, I see text, numbers and checkbox for the same column (Step 1, Step 2, Step 3 and Step 4)… What do you actually want to see?

Couple things can be done, but overall the efficiency gained is really low… You will always need to tell to smartsheet your wanted conditions.

Reading your comments, it looks like higher steps cannot be completed before previous step are done.

So by this logic, I would only look at the highest steps first.

= IF([Step 4]@row=1; 1; IF([Step 3]@row=1; 0.95; IF([Step 2]@row=1; 0.75; IF([Step 1]@row=1; 0.2; 0))))

Now if you want to strenghten your project plan by preventing checking higher steps without completing the previous steps, I would recommand:

1. Conditionnal formatting to visually highlight the problem;
2. Automation: When higher steps are check, smartsheet automatically re-check the lower steps to ensure redundancy;
3. Automation: Locking the line once step 4 is check to avoid mistakes.

If you want your cell to show a error message, then you must code it in the formula.

Hope it helps!

Jonathan D.

Hi!

Currently, I have a formula in place to indicate R,Y,G Health based on the Start/Due Date. Is it possible to add an =OR function so that if "Status" column is switched to complete the Health will indicate green.

Sorry .. very new to this.

I'm trying to make a Metrics sheet to use on a dashboard. I would like to count all of the instances of each (text) heading which fall during this month, then a second column for last month.

I can't get the dates to work so I created helper columns with the month, year, and month+year combined (ex April 2025 would be 42025).

Here is one formula that works: (Count Column is just a column containing the number 1 because Countifs was not working with the same inputs)

SUMIFS({Count Column}, {Defect Name}, [Defect Name]@row, {Year Helper}, =2025)

Here is what I want to work, but is returning #INCORRECT ARGUMENT SET

SUMIFS({Count Column}, {Defect Name}, [Defect Name]@row, {Month/Year Helper}, =42025)

or

SUMIFS({Count Column}, {Defect Name}, [Defect Name]@row, {Year Helper}, =2025, {Month Helper}, =4)

I have checked my references, and they are correct and spelled correctly. Every time I try to add a 3rd range/criterion, I get error messages, even if they work individually. The helper columns are text columns containing formulas

EDIT As soon as I posted this, I went back to the sheet and all of the formulas are working. No idea what happened. I didn't change anything