join collect several variables
From a metrics sheet I am looking at another sheet with a formula which gathers the docket number if the date is before today and it returns the result perfectly. 12234, 67899, 98765
=JOIN(COLLECT({Docket}, {DueDate}, <TODAY()), ", ")
I am trying to modify this formula to return 2 variables (docket + Client) when date is before today but cant figure it out. looking for 12234_honda, 34567_Acme, 56789_Ford
=JOIN(COLLECT({Docket},{Client}, {DueDate}, <TODAY()), ", ") or
=JOIN(COLLECT({Docket}+{Client}, {DueDate}, <TODAY()), ", ")
Best Answer
-
Fortunately this can be converted to a column formula, so really you should just have to create the column, enter the formula once, then convert to a column formula and move on to the next sheet.
Answers
-
You would need to create the base text strings on the source sheet in a new column.
=Docket@row + "_" + Client@row
Then you can reference this new column on the source sheet in your JOIN/COLLECT.
-
Thanks Paul, dang, i was trying to avoid this as there are a multitude of trackers that I pull from. I will proceed with this method instead. Thanks for your help on this.
-
Fortunately this can be converted to a column formula, so really you should just have to create the column, enter the formula once, then convert to a column formula and move on to the next sheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!