Join and Collect Function with Hyperlinks

Nick Hosein

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

J. Craig Williams

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

Nick Hosein

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?

J. Craig Williams

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.

 

Nick Hosein

Thanks for the info Craig.

J. Craig Williams

You are welcome.

Craig