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
-
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
-
I should add that if the most recent "Delivery Location" does not include SOS, the returned data should be blank.
-
What exactly are you wanting to pull from which cell?
-
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.
-
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))
-
That works. Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!