Index(Collect) - Need to pull a list of projects done in 2022

I have a datasheet with a list of closed projects in 2022. I need to pull from that sheet only the 2022 closed projects that occurred 12 months ago from today.

I have a helper sheet, that shows 2022 as the year 12 months ago (manually entering that) and the month 12 months ago (also manually entering that).

now need to pull from the datasource only projects occurring in May , 2022

I can't even get it to give me a list of the 2022 projects, need some help please

Range 5 in Sales/Closed is the Project Name

Range 1 is the Year of the Project

Find more posts tagged with

Accepted answers

All comments

Jason Tarpinian

Seems like there are a few different things you are trying to do. An important part of all the date range formulas is to collect up the ISDATE(@cell), otherwise you'll get a "INVALID DATA TYPE" error, because Smartsheet will always see the blanks at the end of a sheet as values and can't convert them with the MONTH and YEAR functions. With this solution as well, you don't have to split the year/month out of the closing date on your source sheet.

To Count Jobs in a Month/Year =COUNTIFS([Range 5 - Date Closed]:[Range 5 - Date Closed], ISDATE(@cell), [Range 5 - Date Closed]:[Range 5 - Date Closed], MONTH(@cell) = [Test List]$1, [Range 5 - Date Closed]:[Range 5 - Date Closed], YEAR(@cell) = [Test List]2)

Can use the JOIN function to list them all in a single cell. In this formula I used ", " as the delimiter, but one of my favorites is to use CHAR(10), which is a line break to make it look a little nicer (be sure to wrap text if you would like to go with this option) =JOIN(COLLECT([Project Name]:[Project Name], [Range 5 - Date Closed]:[Range 5 - Date Closed], ISDATE(@cell), [Range 5 - Date Closed]:[Range 5 - Date Closed], MONTH(@cell) = [Test List]$1, [Range 5 - Date Closed]:[Range 5 - Date Closed], YEAR(@cell) = [Test List]2), ", ")

Last option would be if you want to make a list on individual cells. This uses a helper column, as INDEX in your original question will return a single value, not a list. So we use a helper column, "Site #" to return the nth value in the collection =IFERROR(INDEX(COLLECT([Project Name]:[Project Name], [Range 5 - Date Closed]:[Range 5 - Date Closed], ISDATE(@cell), [Range 5 - Date Closed]:[Range 5 - Date Closed], MONTH(@cell) = [Test List]$1, [Range 5 - Date Closed]:[Range 5 - Date Closed], YEAR(@cell) = [Test List]$2), [Site #]@row), "")

To better help show where all of my reference are from. The ranges would essentially be :

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.