I would like to make an IF index collect

Mathew Palter
Mathew Palter ✭✭✭✭
edited 12/22/22 in Formulas and Functions

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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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.


    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!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!