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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 217 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!