Join and Collect Function with Hyperlinks
Hello All,
I got my function to work the way I wanted it to, but it did not keep the hyperlinks when it gave me the values. Is there a way to keep the hyperlinks or add them back when using Join(Collect())?
=JOIN(COLLECT({Sandbox - DR - Deliverable}, {Child SKU}, =[Child SKU]1, {Sandbox - Delivery Readiness Tracker Range 2}, ="Delivery Workflow"), SUBSTITUTE([Carriage Return]$1, "-", ""))
Thanks,
Nick
Comments
-
I don't think you can reformat a hyperlink using X-Sheet References.
What you can do is to rebuild the hyperlink on the destination sheet.
It would help if you add an example of your output, as a formula is cryptic without seeing the data or result. Screen shots work too.
Craig
-
I have uploaded two screenshots. The first screenshot is where we are collecting the data from. Notice how "Awesome deliverable" is hyperlinked. We want to grab everything within a SKU name, in this case 3rd Party Inventory, and filter it down to only the Content Type of "Delivery Workflow". The result should be whatever is in the Deliverable column, which is "Awesome deliverable".
We have a working function, but it does not bring over the hyperlink. Is there another function we can use that will?
-
OK, that helps.
X-Sheet References ALWAYS* bring over the DISPLAY VALUE. In many cases, there is also ACTUAL VALUE in the cell.
For example:
- email address in Contact List type
- timestamp in Date/Time column
- formula in a cell with formula
- URL in hyperlink test
(formatting is a different issue as well)
In most cases, it is difficult or impossible to extract the ACTUAL VALUE via a formula.
For hyperlinks, if there is way to enter the URL directly (at Text, instead of in the hyperlink dialog), that can be brought over and made functional on the destination sheet.
I hope that helps.
Craig
*At least, I haven't found a case where this is not true.
-
Thanks for the info Craig.
-
You are welcome.
Craig
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!