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

Options

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.

Best Answers

  • Richard Heath
    Richard Heath ✭✭✭✭✭✭
    Answer ✓
    Options

    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?

Answers

  • Richard Heath
    Richard Heath ✭✭✭✭✭✭
    Answer ✓
    Options

    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?

  • Richard Heath
    Richard Heath ✭✭✭✭✭✭
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!