Collect data from a Child in one sheet and return it to another based on a number of criteria

Richard Heath

I have a need to collect data from one sheet and display it on another, but the data must match a number of criteria, and the data to be collected is a CHILD. Ideally, the user will;

1. Input a Contract Number in Yellow on the Cashflow Sheet. This results in data from above populating the row (e.g Dec-2018 through to Aug-2019) and is a VLOOKUP, so the Yellow cell is variable with up to 30 different Contract Numbers/Names
2. The new formula would sit in the "Actual" row in the Pink Sheet and would then populate by looking up another Sheet (Contractor Payments), identify PARENT number 6, and match the dates and populate with the associated value.
3. There was no Actual Payment in Dec-2018, so the result is $0
4. Jan-2019 on the pink sheet should return $55,658 etc

Your assistance is appreciated.

Regards

Richard.

Richard Heath

I have almost got the formula working, however I've identified that as the Contractor Payments Sheet (below) doesn't include the Contract Number in every row (only the Parent) the following formula doesn't pick up the correct values;

=SUMIFS({Payments Register - Approved Claim Column}, {Payments Register - Period Column}, [Column5]45, {06 - Payments Register Range 2}, $Contract42)

I have therefore manually entered the Contract Number (6) into the CHILDREN (marked-up in Yellow) - this results in the correct data being fed through to the Cashflow sheet but isn't practical.

Other than inserting a hidden helper column which duplicates the PARENT Contract Number column, has anyone got any suggestions on how I can modify the SUMIFS formula above to only look at the CHILDREN if the PARENT matches the cell on the first sheet?

Paul Newcome

Cross sheet references do not support Hierarchy functions such as CHILDREN() or PARENT(). You would need that data somewhere on the source sheet.

If you are unable to add a column to the source sheet, you could still enter a formula in the child rows of [Contract Number] column on the source sheet that is

=PARENT()

You could then use conditional formatting to say something along the lines of if [Claim #] is not blank, turn the font color in the [Control Number] column white. This will effectively hide the duplicated numbers in the child rows.

Richard Heath

I have almost got the formula working, however I've identified that as the Contractor Payments Sheet (below) doesn't include the Contract Number in every row (only the Parent) the following formula doesn't pick up the correct values;

=SUMIFS({Payments Register - Approved Claim Column}, {Payments Register - Period Column}, [Column5]45, {06 - Payments Register Range 2}, $Contract42)

I have therefore manually entered the Contract Number (6) into the CHILDREN (marked-up in Yellow) - this results in the correct data being fed through to the Cashflow sheet but isn't practical.

Other than inserting a hidden helper column which duplicates the PARENT Contract Number column, has anyone got any suggestions on how I can modify the SUMIFS formula above to only look at the CHILDREN if the PARENT matches the cell on the first sheet?

Paul Newcome

Cross sheet references do not support Hierarchy functions such as CHILDREN() or PARENT(). You would need that data somewhere on the source sheet.

If you are unable to add a column to the source sheet, you could still enter a formula in the child rows of [Contract Number] column on the source sheet that is

=PARENT()

You could then use conditional formatting to say something along the lines of if [Claim #] is not blank, turn the font color in the [Control Number] column white. This will effectively hide the duplicated numbers in the child rows.

Richard Heath

Hi Paul,

Thanks for the advice - I resorted to the PARENT() formula in the Contract Number column and set a conditional format to white text to hide as suggested.

This all works now with custom graphs on a Dashboard based on the input of a Contract Number on my sheet.

Appreciate your help.

Regards,

Richard.

Paul Newcome

Happy to help! 👍️