I would like to make an IF index collect

I am working on creating an IF(ISBLANK({cross sheet reference of column}), " ", Index(Collect({ NAME},{cross sheet Helper column}, helpercolumn@row,{cross sheet status}, "Status, {CrossSheetDate}, Contains("/",{CrossSheetDate}),1)

I get an error for the contains part and the formula works without the date, but I am trying to use the date as a trigger. If anyone has any solutions I would Appreciate it

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@Mathew Palter

Try changing your CONTAINS to CONTAINS("/", @cell). You're already specifying the range, and using CONTAINS as the criteria for that range. Using @cell within CONTAINS tells the formula to consider the CrossSheetDate value in every row that meets the previous sets of criteria range/criteria.

Alternatively, if CrossSheetDate is really a date type column with date values in it, you could use {CrossSheetDate}, ISDATE(@cell) instead of the CONTAINS. The ISDATE function just checks to see if the value in that column is a date value.

Paul Newcome

In addition to @Jeff Reisman's suggestion, you also need a closing parenthesis for the COLLECT function before specifying the row number in the INDEX function.

=INDEX(COLLECT(............................., CONTAINS(..........)), 1)

All comments

Jeff Reisman

@Mathew Palter

Paul Newcome

In addition to @Jeff Reisman's suggestion, you also need a closing parenthesis for the COLLECT function before specifying the row number in the INDEX function.

=INDEX(COLLECT(............................., CONTAINS(..........)), 1)

Jeff Reisman

https://community.smartsheet.com/discussion/comment/355247#Comment_355247

Good catch!

Paul Newcome

@Jeff Reisman I almost missed it because of that curly bracket in the original post. Hahaha

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.