Excel hyperlink

  • Excel hyperlink

    Posted by Gary Stewart on May 1, 2023 at 8:19 am

    Hi everyone,

    I think this is where we can send out questions? Or if there’s a more appropriate place let me know.

    Just an easy one.

    I would like to simply create a link in a ‘sheet #’ columned cell that can quickly take a person to the page in Bluebeam where that line item is referencing. No extras, just takes you to that page and that’s it. Can this be done without requiring a markup on that page in BB?

    Thank you! 🙂

    Isaac Harned replied 1 year ago 4 Members · 19 Replies
  • 19 Replies
  • Doug McLean

    Member
    May 1, 2023 at 9:31 am
    Points: 13,005
    Rank: UC2 Brainery Blue Belt III UC2 Brainery Blue Belt III

    Hi Gary,

    Yes.
    In Excel there is a formula called Hyperlink which will allow you to do this.

    What you will have to do though is first break the drawing package into individual files.

    HYPERLINK requires one argument, plus an optional argument, link location, [friendly name]

    The Link location is the filepath; just copy and paste it in. The friendly name is just that, an easily recognizable name

    Here is a really easy tutorial to get you started https://exceljet.net/functions/hyperlink-function

  • Troy DeGroot

    Organizer
    May 1, 2023 at 1:15 pm
    Points: 21,336
    Rank: UC2 Brainery Brown Belt I UC2 Brainery Brown Belt I

    Great to have you in the group @gstewart

    I’m not sure you can link to a certain page without a markup, certainly excited to see how others respond.

    As for the right place to post within the group, there is no strike you dead rules here, but typically Bluebeam questions would go in the General Questions group or one of the workflow based groups when applicable. The UC2 Community group is more for connecting with other users. That’s where I will introduce new content, recordings or events.

    I appreciate you posting your questions no matter where you put it!

    • Gary Stewart

      Member
      May 1, 2023 at 3:40 pm
      Points: 213
      Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

      Appreciate the reply. I guess I can just use the highlighter markup?, would that help? How may I link that to an excel cell?

      Thanks again

    • Gary Stewart

      Member
      May 1, 2023 at 3:44 pm
      Points: 213
      Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

      So what could be a quick way to do this if I were to just say use the highlighter? Would that make it easier to quickly create a linking reference?

      Thanks again!

  • Doug McLean

    Member
    May 2, 2023 at 8:48 am
    Points: 13,005
    Rank: UC2 Brainery Blue Belt III UC2 Brainery Blue Belt III

    You don’t have to place a markup if you don’t want to.

    In the Excel cell you’re going to type =HYPERLINK(<filepath>, [friendly name]).

    This will open the drawing from Excel.

    • Gary Stewart

      Member
      May 2, 2023 at 1:46 pm
      Points: 213
      Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

      There should be a way to link to pdf and page number; i.e. file location and #page=26 or something like this.

      • Doug McLean

        Member
        May 3, 2023 at 9:54 am
        Points: 13,005
        Rank: UC2 Brainery Blue Belt III UC2 Brainery Blue Belt III

        I don’t believe there is, not from Excel.
        This is why you have to break down your drawing package into individual pages. That way each page becomes it’s own file.

        • Isaac Harned

          Member
          May 3, 2023 at 12:21 pm
          Points: 6,571
          Rank: UC2 Brainery Purple Belt I UC2 Brainery Purple Belt I

          As I understand it, this should be possible, but would not use bookmarks. It would use “Named Destinations”, or as Revu calls them, places. You can find these in the links tab. I can’t seem to get the feature to work though, and some forums are saying that it only works for URL links. Here is an article:

          https://community.adobe.com/t5/acrobat-discussions/linking-to-within-a-pdf-using-named-destinations/td-p/1163038

          See below screenshot for description of “Places” in Revu

          • Isaac Harned

            Member
            May 3, 2023 at 12:22 pm
            Points: 6,571
            Rank: UC2 Brainery Purple Belt I UC2 Brainery Purple Belt I

            Here are all the formats I tried to make it work for default open, switched my convention from single letter to “Grille” later

            H:\HOU\Office\PROJECTS\2210327\DATA\Submittal\23 37 00-1 Air Inlets and Outlets – PD_SSA_CD.pdf#page=D

            \\Fs01\data\HOU\Office\PROJECTS\2210327\DATA\Submittal\23 37 00-1 Air Inlets and Outlets – PD_SSA_CD.pdfpage=20&nameddest=D

            H:\HOU\Office\PROJECTS\2210327\DATA\Submittal\23 37 00-1 Air Inlets and Outlets – PD_SSA_CD.pdf#page=20&nameddest=D

            \\FS01\DATA\HOU\Office\PROJECTS\2210327\DATA\Submittal\23 37 00-1 Air Inlets and Outlets – PD_SSA_CD.pdf#20&nameddest=D

            file://\\FS01\DATA\HOU\Office\PROJECTS\2210327\DATA\Submittal\23 37 00-1 Air Inlets and Outlets – PD_SSA_CD.pdf#page=20&nameddest=D

            \\FS01\DATA\HOU\Office\PROJECTS\2210327\DATA\Submittal\23 37 00-1 Air Inlets and Outlets – PD_SSA_CD.pdf#page=20&nameddest=D

            \\FS01\DATA\HOU\Office\PROJECTS\2210327\DATA\Submittal\23 37 00-1 Air Inlets and Outlets – PD_SSA_CD.pdf#page=20&nameddest=Grille

            \\FS01\DATA\HOU\Office\PROJECTS\2210327\DATA\Submittal\23 37 00-1 Air Inlets and Outlets – PD_SSA_CD.pdf#Grille

            \\FS01\DATA\HOU\Office\PROJECTS\2210327\DATA\Submittal\23 37 00-1 Air Inlets and Outlets – PD_SSA_CD.pdf#page=20

            • Doug McLean

              Member
              May 3, 2023 at 1:54 pm
              Points: 13,005
              Rank: UC2 Brainery Blue Belt III UC2 Brainery Blue Belt III

              Did any of them work?

            • Isaac Harned

              Member
              May 3, 2023 at 5:29 pm
              Points: 6,571
              Rank: UC2 Brainery Purple Belt I UC2 Brainery Purple Belt I

              Negative, really hoping someone out there might know more. I even pulled the doc up in Adobe to verify it recognized the named location. Makes no sense since Revu supports the location.

            • Doug McLean

              Member
              May 4, 2023 at 9:42 am
              Points: 13,005
              Rank: UC2 Brainery Blue Belt III UC2 Brainery Blue Belt III

              I don’t think the problem is Revu, the problem is Excel and the way the HYPERLINK formula has to be written.

            • Isaac Harned

              Member
              May 4, 2023 at 10:48 am
              Points: 6,571
              Rank: UC2 Brainery Purple Belt I UC2 Brainery Purple Belt I

              I don’t think so, the attempts I did were not writing code, but manually creating the link and opening through outlook or word. I can write the hyperlink formula to work once I know how the link needs to be formatted in the first place.

  • Doug McLean

    Member
    May 2, 2023 at 10:10 am
    Points: 13,005
    Rank: UC2 Brainery Blue Belt III UC2 Brainery Blue Belt III

    You can also get really creative and use Excel commands to get you a dynamic file name.
    If you have O365 you can use the TEXTBEFORE and TEXTAFTER commands, or the LEFT, RIGHT and MID functions.

  • Isaac Harned

    Member
    May 2, 2023 at 10:49 am
    Points: 6,571
    Rank: UC2 Brainery Purple Belt I UC2 Brainery Purple Belt I

    Like Doug says above, there is the hyperlink function, you can place a formula in lets say column P (depends on how long your addresses are, whatever column gets it out of the way) that creates a shortened hyperlink for the addresses pasted in column A. You can then either hide cells, hide tab or copy and re-paste the links. This would be the simplest way, but there are also ways to create a PDF index based on your files as well.

    The simple process would look like this, multi-select files and “Copy Full Pathnames” as shown in screenshot below. Then open Excel workbook and paste into desired column.

    For the following formula to work, it assumes that there is a separator between the sheet number and name ” – “. It will look for this separator and shorten the link to only display the sheet number. If there is no data, there will be no result shown:

    =IFERROR(HYPERLINK(A1,LEFT(RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,”\”,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))))),FIND(” – “,RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,”\”,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))))))-1)),””)

    • Isaac Harned

      Member
      May 2, 2023 at 10:52 am
      Points: 6,571
      Rank: UC2 Brainery Purple Belt I UC2 Brainery Purple Belt I

      By the way, I am no coder, if curious how I came up with this, check out my conversation with ChatGPT 😂

    • Doug McLean

      Member
      May 2, 2023 at 11:00 am
      Points: 13,005
      Rank: UC2 Brainery Blue Belt III UC2 Brainery Blue Belt III

      learn the TEXTBEFORE and TEXTAFTER commands…. SO much simpler than the dreaded LEFT, MID and RIGHT…. lol

      • Isaac Harned

        Member
        May 2, 2023 at 12:11 pm
        Points: 6,571
        Rank: UC2 Brainery Purple Belt I UC2 Brainery Purple Belt I

        Definitely shortens it:

        =IFERROR(HYPERLINK(A1,TEXTBEFORE(RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,”\”,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))))),” – “)),””)

  • Doug McLean

    Member
    May 4, 2023 at 9:50 am
    Points: 13,005
    Rank: UC2 Brainery Blue Belt III UC2 Brainery Blue Belt III

    I actually do this workflow.

    One of the (many) advantages of working in a Set is the ability to export a drawing log. One of the things you’re allowed to export is the File Path, so if you export this to either an Excel .xlxs or .csv file, Excel writes the hyperlink formula for you (showing the relative path).

    Then you just have to do a little bit of work to get it into wherever you need it, but its not all that hard.

Log in to reply.