Join and Collect Function with Hyperlinks

09/27/18 Edited 12/09/19

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

Popular Tags:

Comments

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    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

  • edited 09/28/18

    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 WilliamsJ. Craig Williams Top Contributor

    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.

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    You are welcome.

    Craig

Sign In or Register to comment.