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)
