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.