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

zoe.h
zoe.h
edited 02/10/24 in Formulas and Functions

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!

Tags:

Answers

  • Andrée Starå
    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)

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!