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

Options

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)

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!