JOIN COLLECT Cross-Sheet Formula Not Returning Content
Happy 2024, everyone!
I'm trying to find the error in a JOIN(COLLECT) cross-sheet formula and am overlooking something. Here's the setup:
Sheet 1 - The JOIN(COLLECT) cross-sheet formula is in the Status History field.
Sheet 2 - The helper sheet where a column formula is used in the Status History field to combine the Status and Status Update Date.
The JOIN(COLLECT) cross-sheet formula in Sheet 1 is supposed to pull all the content from the Status History field in Sheet 2, but the formula is not pulling any content from the Status History field. The formula is:
=JOIN(COLLECT({Sheet 1-Status History}, {Sheet 1-ID}, ID@row), CHAR(10))
Thanks in advance for your help!
Lori
Answers
-
How exactly are the ID columns in both sheets being populated?
-
ID fields in Sheet 1 and in Sheet 2 are autonumber fields that are in sync.
Each row in Sheet 1 copies to Sheet 2 when Status Updated Date is completed.
-
I'm not sure I follow.
-
The numbers in the ID fields in both sheets are set as autonumber fields. Those fields are in sync, meaning the autonumbers match.
-
Do any of those numbers have a leading zero?
-
No leading zeros.
-
Are you able to provide screenshots of both sheets that show a set of numbers that should be matching but are not?
-
The screenshots in my original post are the exact data and field names in my sheets. Is there other information needed?
-
Is it possible to see the data in the actual sheets though? Even some sample entries would be fine, but it would be helpful to see them in Smartsheet itself.
-
Thanks, Paul.
I've heard this morning from Smartsheet support that the autonumber field was being read as text because the numerical places was changed from 0 to 4 to start with 1000 as the first number. Apparently autonumber fields must be left with 0 numerical places to be read as numbers.
Once the autonumber field was updated, the formula worked; however, I'm certainly submitting enhancement requests to change the flawed design of the autonumber field so it reads numbers as numbers regardless of the number of numerical places.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!