INDEX-COLLECT Formula Returning #INCORRECT ARGUMENT

Options

Hi Smartsheet Community,

I've already perused a number of Community postings related to this formula, but I can't seem to work it out.

On my source sheet, I have the following columns:

1) KES Number

2) Date

3) Time Spent

On my target sheet, I have these same columns in addition to other data. So far, this is the formula I'm using: =INDEX(COLLECT({PAR Submissions Range 1}, {PAR Submissions Range 2}, [15th - Date]@row, {PAR Submissions Range 3}, [KES Number]@row, 1)), where:

PAR Submissions Range 1 is the Time Spent column, PAR Submissions Range 2 is the Date column, and PAR Submissions Range 3 is the KES Number column in the source sheet.

The only thing I can think of is that the dates generated in the target sheet are the result of column formulas. However, when I remove the column formula and enter the date alone, I still get the error message.

Of note, both KES Number columns are the primary column.

Any help or assistance with this would be greatly appreciated.


Grant

Tags:

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @GrantD

    What type of column is the column you've got this failing formula in? It can't be a date column because you're trying to collect a time spent value. So if you've got it formatted as a date column that could be the problem.

    Another thing you can try if the first thing wasn't it, is adding a column and using the formula

    =IF(ISDATE({PAR Submissions Range 2}), "is date", "not a date")

    Do that for each of your date fields. This will see if the cell is being looked at as a date or not. Also, is the column for PAR Submissions Range 2 a date column? It should be. If they're not dates then you need to fix that somehow.

  • GrantD
    GrantD ✭✭
    Options

    @Mike TV

    Hi Mike, thank you for your time in responding to my question. I've copied and pasted your suggested formula to my sheet and I get another #INCORRECT ARGUMENT response.

    Do you happen to know if there is a restriction to the format for dates? I currently have mine in long form, e.g. Fri, 12/15/2023.

    I've verified that each column is of the correct type, Date or Text/Number, respectively.

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @GrantD

    You shouldn't be getting an Incorrect Argument error for the IF ISDATE formula. Try typing it out manually instead of copy-pasting from the forums.

    As far as I understand, having the column set to long form dates shouldn't affect anything. It's just the way it's displayed. "Thursday Dec 15, 2022" is the same as "12/15/22".

  • GrantD
    GrantD ✭✭
    Options

    @Mike TV

    I've manually entered the formula and it returned the same result. I also tried toggling the source sheet 'Date' column to Text/Number and then back to Date.

    I'm really stumped on this one, I can usually figure these things out. My apologies for being the 'problem user' today. 🤦

  • GrantD
    GrantD ✭✭
    Options

    @Mike TV

    It appears that the IS DATE formula works when referencing a singular cell in the source sheet or the target sheet. However, when I attempt to use a range, I get the #INCORRECT ARGUMENT error.

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @GrantD

    Ah yes. That would be the cause of the IF(ISDATE problems. So if you point it to one cell in that range's column does it say it's a date or not a date? If it's a date then I don't know what your solution would be, sorry. If it's not a date, then that needs to be corrected somehow.

  • GrantD
    GrantD ✭✭
    Options

    @Mike TV

    When referencing a singular cell it returns "is date." I very much appreciate all of your help this evening. I'll continue working on a solution and post one here if I can figure it out.

  • GrantD
    GrantD ✭✭
    Options

    @Mike TV

    Finally got it to work, although I'm still not sure where the IF(ISDATE() error was originating. I actually found the solution in one of your old responses on another thread, thought you might enjoy that.

    =INDEX(COLLECT({PAR Submissions Range 4}, {PAR Submissions Range 2}, [1st - Date]@row, {PAR Submissions Range 1}, [KES Number]@row), 1)

    where: PAR Submissions Range 4 is Time Spent, PAR Submissions Range 2 is the Date Submitted, and PAR Submissions Range is the KES Number.

    Again, thank you for all your assistance! Hope you're well.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!