How do I "check a box" in one sheet based on criteria from a separate sheet?

AFriedl

Ideal Outcome: I get weekly training reports generated to sheet "x" and when someone is marked as "complete" in the training reports, I want to have a box in sheet "y" be checked. Is there a way to automate this process without copying loads of unnecessary data into the tracking sheet (sheet "y")

Hunter Taylor

Hi AFriedl

Yes, you can automate this process in Smartsheet without copying unnecessary data by using cross-sheet formulas or a combination of automation rules and a helper column. Here's how to set it up:

Option 1: Cross-Sheet Formula

1. Set up a Unique Identifier: Ensure both sheets ("x" and "y") have a common unique identifier, such as an employee ID or name.
2. Link Data Between Sheets:
   • In sheet "y", add a column (e.g., "Training Status Link").
   • Use a cross-sheet formula like =VLOOKUP([Unique ID]@row, {Sheet X Range}, Column Number, false) to pull the "Training Status" (e.g., "Complete") from sheet "x".
3. Add a Checkbox Column in Sheet "y":
   • Create a checkbox column (e.g., "Training Complete").
   • Use a formula like =IF([Training Status Link]@row = "Complete", 1, 0) to check the box automatically when the status is "Complete".

Option 2: Automation Rule with Helper Column

If cross-sheet formulas aren’t an option:

1. Create a Helper Column in Sheet "x":
   • Add a column in sheet "x" (e.g., "Flag for Sheet y").
   • Use automation in sheet "x" to flag rows when the "Complete" status is detected.
2. Set Up a Data Shuttle or Move Automation:
   • Use Smartsheet’s Data Shuttle (if available in your plan) or automation rules to transfer only flagged rows (or specific columns) to sheet "y".
3. Link the Data in Sheet "y":
   • In sheet "y", match the transferred data (e.g., by Unique ID) to update the "Complete" checkbox column.

Considerations:

• Cross-Sheet Reference Limits: Smartsheet limits the number of cross-sheet cell references, so keep this in mind if working with large datasets.
• Permissions: Ensure both sheets have proper sharing permissions for users who need to view or edit the data.

Hopefully this helps. If you need further assistance just let me know and I'd be happy to help.