Index Match with multiple Parent/Child rows in a sheet.

MHalvey

Hello all, I'm looking for some advice on a tiered IF statement using index/match and CHILDREN formulas.

My case: I receive numerical data from another metric sheet that copies it snapshots into this archive once a month. There may be times where we need to edit the historical data. To ensure the % columns are updated I wanted to apply column formulas and would need multiple IF statements due to Parent and Child setup. There are 2 IF statements in this formula string and will run in the row depending on the number sequence selected (HC-Level column).

• All children rows are set to 2 and is a simple percent returned (=Denied@row / Total@row).
• The parent rows are set to 1 and require the same style of formula but we are not wanting to show Vendor 4 in each monthly snapshot total and would like its total removed from the parent total. (Denied@row / (Total@row - INDEX(CHILDREN(Total:Total), MATCH("Vendor 4", CHILDREN(Vendor:Vendor), 1))

My thinking was to use the CHILDREN formula to look at the direct child in the parent rows, however once I start adding in additional months with parent and children rows, the formula reads ALL the children and not just the direct ones. Is there away to subtract a specific vendor totals from the whole (each month) and use a column formula for it? One last item to note, the vendor could jump around in the parent/child group each month so it would need to look for the specific name which is why I thought index/match.

If anyone has worked with multiple parent/child's in a sheet and rolling up data like this, I'd love to hear your workarounds for a formula.

Thank you - Michael

Protonsponge

Hello @MHalvey,

Could you have a helper column with the formula. In my demo I called it [Month/Year Helper]

=IF([HC-Level]@row = 1, [Primary Column]@row, PARENT([Primary Column]@row))

You could then use this helper column as a criteria in an INDEX/COLLECT formula such as this one:

=IF([HC-Level]@row = 1, Denied@row / (Total@row - INDEX(COLLECT(Total:Total, Vendor:Vendor, "Vendor 4", [Month/Year Helper]:[Month/Year Helper], [Primary Column]@row), 1)), IF([HC-Level]@row = 2, Denied@row / Total@row))

I think this is what you are looking for and I hope it works for you - it seems to be working in the demo below.

Helper column set up:

%denied column with above formula:-

I hope that is helpful to you in someway,

Protonspounge