Why is my SMALL COLLECT not working? When JOIN COLLECT works fine?
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
-
Are you able to provide some screenshots for reference?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
So, I needed to add VALUE before my so-called numbers, to make them actual numbers.
But I still get the error.
-
Are you able to provide some screenshots for reference?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!