INDEX - Collect question

Hi there,

I have a project where in the project plan, based on the start date selected for a task, I'd like Smartsheet to reference another sheet (which is the table of Sprint, Start Date, Proposed Completion Date, Sprint Label) and populate the Sprint Column with the correct Sprint Label).

So the formular should check Start Date in the project plan is greater than or equal to the Start Date and less than or equal to the Proposed Completion date and return the relevant Sprint Label, into the column labelled 'Sprint' in the project plan.

Is anyone a GURU with the Index(Collect) function? This will remove the manual work of PMs having to manually check the dates to populate Sprint number.

Find more posts tagged with

Sheets

Formulas

functionality

Automations

Project Management

Accepted answers

All comments

Erik Camacho

Hello @RosalynW,

I feel that you can accomplish this with a IF and formula.

=IF(AND([Start Date]@row <= [Start Date]@row , [Start Date]@row >= {Proposed Completion Date}@row ), {Sprint Label}@row , "")

Create the formula in the Sprint Column in your project plan and possibly make it a column formula.

Matt_A

This is what I used for one of my projects to reference an outside sheet:

=IFERROR(INDEX(COLLECT({Helper Data Range 1}, {Helper Data Range 2}, @cell <= Date@row , {Helper Data Range 3}, @cell >= Date@row ), 1), "No Sprint Assigned")

I have a seperate sheet with each sprint name going out for many years (Range 1), Start Date of that sprint (Range 2) and End Date of that Sprint (Range 3)

RosalynW

Thanks Matt - I'll give that a try 😉

RosalynW

Thanks Erik, I'll try Matt's suggestion first and then revert to yours if I can get the Index function to work. I appreciate the suggestion 😊

Paul Newcome

I also suggest an INDEX/COLLECT.

=IFERROR(INDEX(COLLECT({Sprint Label}, {Sprint Start}, @cell <= [Start Date]@row, {Sprint End}, @cell >= [Start Date]@row), 1), "value if no sprint listed in reference table")

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.