Returning a symbol value for parent-child relationship across smartsheets

Hello,

I have two sheets. One provides information for each project by the project vendor, the other links a project to it's multiple vendors. In the second sheet, I want to pull over the project at with at risk TAT status symbol for that project, so I can see across vendors the total project risk.

Here is Sheet 1 (summarises data at the Vendor level). Each vendor has several dependent child projects.

Here is Sheet 2, which Project level data across vendors. I've highlighted the ones that have a matching sheet 1 risk symbol, I would like to automatically pull through, if there is a match to sheet 1 project listed for the child vendor.


Can you help me with this, I've been trying to solve for it, and I just can't get anywhere, and am pretty sure it can be done, I just haven't cracked how.


thank-you,

s

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Susan Pattison

    Try this. Make sure your Sheet2 Project Vendor Risk Level is a Symbol column with RYG.

    This will only put a symbol in the child rows. Your Parent rows will remain blank.

    =IFERROR(IF(COUNT(CHILDREN([Project / Vendor Name]@row)) = 0, INDEX({Sheet 1 #Project with at Risk TAT}, MATCH(PARENT([Project / Vendor Name]@row), {Sheet 1 Projects}, 0))), "")

    From the screenshot there wasn't evidence of duplication of Project numbers and Vendors, so the Index/Match will work. If there is more than a single criteria to match (in this case the formula only looked at the project number), then instead of Index/Match, we would consider one of the Collect functions.

    Does this work? Let me know and we'll continue to tweak until we get it right.

    cheers,

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!