Index/Match with cross sheet references
I have four sheets that I want to summarize in a separate sheet. If I am using an Index/Match formula, can I apply it to Child rows also? On my sheets, I have an amount columns, but some amount cells are sums of the two children rows beneath it. So, when I use the Index/Match formula, it only applies to the main Parent row. I am using cross sheet references. Is this possible? If so, how? Thank you in advance.
Answers
-
MATCH() function automatically finds the first match and stops searching. So if it's the parent level you need to pull in data from, you're all set with just a normal INDEX/MATCH.
If you do need to distinguish the two, I always like have a helper column with the =COUNT(ANCESTORS()) formula, which will return the hierarchy level of the row. Then you can use "Hierarchy = 0" to filter for the parent rows in SUMIFS or COUNTIFS functions.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
@Jason Tarpinian - does that work if on the summary sheet I need the individual amounts from the child rows (Hierarchy =1)? I have two different child values with their own individual values going into one batch (parent - SUM)).
-
If you need the nth child row, you might need to look into using INDEX/COLLECT, if I'm following you correctly. See my example below. In this case "Parent Grouping" and "Lookup Child #" are telling the formula which parent section to look in, and which child # to pull from that grouping. This is the formula in the "Value" column, so Parent 2, 3rd child, returns $2,000.
=INDEX(COLLECT([Return Value]:[Return Value], Parent:Parent, [Parent Grouping]@row, Hierarchy:Hierarchy, 1), [Lookup Child #]@row)
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 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!