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:

Find more posts tagged with

Formulas

Accepted answers

All comments

bisaacs

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!

Majduleen

it didnt work :(

Gillian C

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 https://help.smartsheet.com/articles/2476176-formula-error-messages

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.