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
Best Answers

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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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)
Answers

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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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)

Good catch!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

@Jeff Reisman I almost missed it because of that curly bracket in the original post. Hahaha
Help Article Resources
Categories
Check out the Formula Handbook template!