Pull Date from Separate Sheet That Matches 2 Row Criteria
So the company I work for has a system of T-Minus dates based on round (easier to understand with screenshot below), since we have repeating tasks round over round. This system really confuses people and it's a PITA to have to constantly look up dates based on these criteria. I have a feeling this is a fairly simple formula for someone familiar with it, but my experience is fairly limited.
Basically, I'd like to input a formula in the Baseline Start Column that will pull info from another sheet based on the Round and T-Minus Start columns.
I've created two separate sheets, one for start and one for finish, so I assume both formulas will effectively be the same. I just want the formula to find the matching Round row and T-Minus Column and pull the correct date for me. In this instance, Round 1 (RD01) and T-38 should return 4/14/22
I'm not sure if I should be using MATCH or COLLECT within INDEX. I've never used any of these before and I'm having a hard time wrapping my head around how to accomplish this.
Thanks!
Best Answer
-
It looks like your "t minus" are incrementing by week (7 days). In that case I would suggest a basic INDEX/MATCH to grab from the -45 column and the add days based on >>((-45 - [T-Minus Start]@row) * 7)<<.
=INDEX({Reference Sheet -45 Column}, MATCH(Round@row, {Reference Sheet Round Column}, 0)) + ((-45 - [T-Minus Start]@row) * 7)
Answers
-
I hope you're well and safe!
You’d use and INDEX/COLLECT combination.
Did that work/help?
I hope that helps!
Be safe, and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
It looks like your "t minus" are incrementing by week (7 days). In that case I would suggest a basic INDEX/MATCH to grab from the -45 column and the add days based on >>((-45 - [T-Minus Start]@row) * 7)<<.
=INDEX({Reference Sheet -45 Column}, MATCH(Round@row, {Reference Sheet Round Column}, 0)) + ((-45 - [T-Minus Start]@row) * 7)
-
Hi Paul, thanks for the response!
I ended up figuring it out myself after a bit of trial and error. This is what I got...
=INDEX({Index Range}, MATCH(Round@row, {1st Column Range}), MATCH([T-Minus Start]@row, {1st Row Range}))
From there I made a second formula that references my T-Minus Finish dates (which is in it's own separate document), instead of start. It's pretty straight forward now that I understand it, but this is my first time using INDEX so it took awhile to wrap my head around it
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 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!