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

Find more posts tagged with

Formulas

Cell linking

Comments

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?

Delivery Tracker (DR) Sheet.png

Sheet with Join and Collect Function.png

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…