INDEX-COLLECT Formula Returning #INCORRECT ARGUMENT

GrantD

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

Mike TV

@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

@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

@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

@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

@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

@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

@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

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