Return a portion of text in a CHILD cell based on Date column

I am trying to automatically pull an airport tricode out of text in a child cell (Delivery Location) based on the most recent Delivery Date. The green row is the parent. Is this possible?


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try something like this...


    =IF(CONTAINS("SOS", INDEX(CHILDREN([Delivery Location]@row), MATCH(MAX(CHILDREN([Delivery Date]@row)), CHILDREN([Delivery Date]@row), 0))), MID(INDEX(CHILDREN([Delivery Location]@row), MATCH(MAX(CHILDREN([Delivery Date]@row)), CHILDREN([Delivery Date]@row), 0)), 5, 3))

Answers

  • Beth C
    Beth C ✭✭

    I should add that if the most recent "Delivery Location" does not include SOS, the returned data should be blank.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What exactly are you wanting to pull from which cell?

  • Beth C
    Beth C ✭✭

    I want to pull text out of the most current "Delivery Location" based on the "Delivery Date" child. If the most recent "Delivery Date" Contains "SOS", I want to pull out characters 5-7 ("DFW" out of "SOS DFW Hold"). If "Delivery Date" does not contain "SOS", the cell should be blank. I want this text to appear in the parent row (the green row) in the "Delivery City" cell.

    In my screen grab, "DFW" would automatically populate in the green cell where it is manually entered now based on the "SOS DFW Hold" in the child row that shows 3/4/22 as the "Delivery Date".

    I think I can figure out how to get characters 5-7 out of the text. It's getting the "Delivery Location" based on the "Delivery Date" that is stumping me.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try something like this...


    =IF(CONTAINS("SOS", INDEX(CHILDREN([Delivery Location]@row), MATCH(MAX(CHILDREN([Delivery Date]@row)), CHILDREN([Delivery Date]@row), 0))), MID(INDEX(CHILDREN([Delivery Location]@row), MATCH(MAX(CHILDREN([Delivery Date]@row)), CHILDREN([Delivery Date]@row), 0)), 5, 3))

  • Beth C
    Beth C ✭✭

    That works. Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!