Why is my SMALL COLLECT not working? When JOIN COLLECT works fine?

James Keuning

I have this formula that works fine when referencing dates, but when I reference a string of numbers, I get #INVALID VALUE.

My formula:

=SMALL(COLLECT({JoinDateString}, {Master Schedule Range 1}, [Primary Column]@row), 1)

Response:

#INVALID VALUE

But this formula:

=JOIN(COLLECT({JoinDateString}, {Master Schedule Range 1}, [Primary Column]@row), ",")

Returns:

2022080500602,,,2022042900605,2022042500606,2022042500607,2022080500608

Which is correct.

When I try this on a local sheet, it works fine, but I cannot get it to work using a cross-sheet reference. Since the JOIN works fine, I assume my range, criterion range, and criterion are fine. And like I said, if I use dates, instead of strings of numbers, the SMALL works.

Here are the values:

2022080500602

empty

empty

2022042900605

2022042500606

2022042500607

2022080500608

Here is the formula that I use for the local test, where the values in Column6 and Column5 are the same as Master Schedule Range 1 and [Primary Column]@row respectively.

=SMALL(COLLECT([Column6]:[Column6], [Column5]:[Column5], test@row), 1)

Paul Newcome

Are you able to provide some screenshots for reference?

James Keuning

So, I needed to add VALUE before my so-called numbers, to make them actual numbers.

But I still get the error.

Paul Newcome

Are you able to provide some screenshots for reference?

James Keuning

Hi @Paul Newcome, thanks for taking the time to reply multiple saves and refreshes, I got frustrated and left. When I came back to it, it was working. This is not the first time that this has happened, and I always wish I had a screen recorder running so I could go back and see if the broken version was something different. But as far as I know, nothing changed between broken and working.