what is the formula to collect Sprint no, based on the start date for a task from another sheet
I'm trying to use =INDEX(COLLECT({Project PI & Sprint-Primary Column}, {Project PI & Sprint-Start Date}, <=[Start Date]@row), {Project PI & Sprint-End Date}, >=[End Date]@row, 1) but its not working
I want to add a formula in this sheet to provide Sprint # based on the start and end date for the task, refrencing to another sheet with all set dates for each sprints
the reference sheet to collect the data from is below:
Answers
-
Hey @Majduleen,
Looks like you're missing a ')' between [End Date]@row and the comma:
=INDEX(COLLECT({Project PI & Sprint-Primary Column}, {Project PI & Sprint-Start Date}, <=[Start Date]@row), {Project PI & Sprint-End Date}, >=[End Date]@row), 1)
Hope this fixes it!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
it didnt work :(
-
Hi @Majduleen
I've rerun the INDEX COLLECT formula for your Sprint dates and it was the same format as suggested by @bisaacs and it works.
The second error that you have is different to the first
So now that you know your formula is in the correct format try checking that everything is spelled correctly in your column names, and that all the date columns are actual date columns etc. and see if that helps?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!