I would like to make an IF index collect

Mathew Palter

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

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)

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)

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