Return value of another column if checkbox is checked

Options

Referencing the below example, I'm looking for a formula that can return values in other columns when the checkbox is marked in the first column. I have a sheet with many tasks pertaining to a project but only certain tasks are tracked by our executive team. In the example below they only want to see Date 1, Date 2 and Status for the tasks with checkboxes.

Currently our team is linking all rows for the desired 3 columns over to another sheet and applying a filter to only show the checked boxes. Every time we add a new project to the list it is a very manual process to add the 3 rows pertaining to the new project for tracking purposes. This is intentionally done in a sheet rather than a report due to conditional formatting that differs from the original sheet.

I tried: =INDEX({Date 1 Column},MATCH(1,{Checkbox Column},0) and got a #NOMATCH error.

I appreciate any help you can offer.

Tags:

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    edited 03/29/22
    Options

    Report may be a good option instead. You can filter only those that have checkbox checked and display whatever columns are of interest and then share the report link with the appropriate audience.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @JCluff

    I hope you're well and safe!

    Here's a possible workaround or workarounds

    • Link everything to the other sheet by using so-called Helper Columns. Add an auto number column in the Source sheet and then add a Text/Number column in the Destination Sheet and add the numbers manually to as many rows that you'll need. You'd then use INDEX/MATCH to connect the sheets. In the Destination sheet, you'd add a filter only to show the rows they want to see.

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    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 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:andree@workbold.com | 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!