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

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
Cashflow Table.png Payment Schedule Sheet.png

Your assistance is appreciated.

Regards

Richard.

Best Answers

  • Richard Heath
    Richard Heath Community Champion
    Answer ✓

    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)

    Annotation 2020-01-29 155106.png

    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?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!