How to use Vlookup or Index/Match to automatically mark a task complete in destination row

zoe.h

Hi!

I have a task tracker in which things are marked "Complete" from a dropdown and then checked off. The checkbox has conditional formatting that puts a slash through the entire row.

Some of these tasks are delineated as "projects", but I've created an automation to copy a row if it is labeled a project, so that anything that is specifically a project gets copied over into a Projects Tracker.

The problem is that I'm unfamiliar with Vlookup or Index/Match, and I'd like the task to be marked complete and slashed through on the destination sheet when this is manually updated in the source sheet. Currently, we're automatically updating both sheets when completing a project, which is inefficient.

This is what the source sheet looks like:

The destination sheet looks similar but only copies over lines that we fill out as Project (PR) in the Task Category column.

I tried an autonumber column but can't get much further than this. Any help would be appreciated!

Andrée Starå

Hi @zoe.h

I hope you're well and safe!

You could connect the sheets using cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure. When you update the source sheet, it will reflect on the destination sheet.

To connect them row by row, you could use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row ID on as many rows as you need in the Destination sheet.

More info.

Here's the structure for an INDEX/MATCH combination.

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0)

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!