Ideas on how to effectively create a new sheet that collects information to update status on another

trafkey

I am struggling to find other answers on the community for this situation.

We are creating a log of all equipment. I am creating a second sheet for when that equipment is removed. When someone submits through a form that equipment is removed, I would like the second sheet to automatically update the status on the original sheet.

We will be maintaining a unique identifier for the equipment that will be the same across both sheets, but I'm struggling with finding what the best solution will be for this.

Ape_Man819

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!

trafkey

Just to make sure that I am understanding this correctly-

Does this still work if the removal log does not have the unique identifier until someone submits it to the removal log?

I'm just making sure that there won't be issues if the removal log is blank, until equipment begins being submitted on it.

Thank you for your explanation!

trafkey

Another question,

I'm now trying to set this up so that it can check 2 criterion. I'm following what Smartsheet says the syntax should be, but it keeps saying it is wrong. Any insight on how to change the syntax so it is better accepted?

=IF(COUNTIFS({Equipment Removal Log Range 1}, [Permit #]@row), [{Equipment Removal Log Range 2}, [Project Name]@row]) > 0, "Removed", "Active")

Paul Newcome

This will work even if the Removal Log is blank. Also try this for the updated formula:

=IF(COUNTIFS({Equipment Removal Log Range 1}, [Permit #]@row, {Equipment Removal Log Range 2}, [Project Name]@row) > 0, "Removed", "Active")