Forum Discussion

julietellis's avatar
julietellis
Copper Contributor
Nov 19, 2020
Solved

How to link multiple excel cells to external file

Hi there, I am looking to upload a spreadsheet of data to an online science database.  To do this, I need to link each cell in a column (of around 600 cells) each to their own relevant document. I ...
excel
Formulas and Functions
  • MindreVetande's avatar
    MindreVetande
    Nov 19, 2020

    julietellis 

    Hi.

    Assume your link looks something like this:

    ='C:\tmp\[doc0001.xlsx]Sheet1'!$A$1

    And you want to change the doc0001 to 0002 and so on.

    This is one way to glue the address together:

    =CONCATENATE("'C:\tmp\[doc",TEXT(ROW(A1),"0000"),".xlsx]Sheet1'!$A$1")

    Copy down

    But this is just a string and Excel wont understand that it is an address that you want to link to. In you wrap the formula within "INDIRECT" you tell excel that it is an address/Link

    =INDIRECT(CONCATENATE("'C:\tmp\[doc",TEXT(ROW(A1),"0000"),".xlsx]Sheet1'!$A$1"))

    Unfortunately INDIRECT() has a big disadvantage. The file you link to must be open....

     

    On  way to handle this is to make the link text (including the 😃

    =CONCATENATE("='C:\tmp\[doc",TEXT(ROW(A1),"0000"),".xlsx]Sheet1'!$A$1")

     

    Copy down as many rows as necessary.

     

    Now. Copy the column with addresses and paste it as text

    Start->paste->paste special->as text

    Now you will have the addresses, but no link in each cell.

    Select your column again and do a search and replace (Ctrl+H). Seach after Equals sign (=) and replace with Equals sign (=). This will force excel to accept every cell as anew entry and convert you text to a real formula/link

MindreVetande's avatar
MindreVetande
Iron Contributor
Nov 19, 2020
**ED** i re-read your question and i think i totally misunderstood what you want. Could you please clarify. Ideally with a small example file. **/ED**
julietellis's avatar
julietellis
Copper Contributor
Nov 19, 2020

MindreVetande 

Hi there,

Thank you for your help on this. 

 

I am trying to link some fasta (.fas) files to cells in column AB. AB contains 4 files I have linked manually but I cannot do this for all 600, as I am sure there is a quicker way. The file name is in the format: <BIGSid>_<isolate name>.fas

 

This BIGSid is also in column C and the isolate name is in column D (also known as aliases).

 

I have attached a picture of the file containing the files I am trying to link too. 

 

I have tried using your Concatenate formula (image also attached) however 'paste as text' doesn't seem to come up as an option for me.

 

Thank you so much for your assistance it's really appreciated 🙂

  • MindreVetande's avatar
    MindreVetande
    Iron Contributor
    Nov 19, 2020

    julietellis 

    Hi. It Looks good.
    I only misunderstood half of your Question. A good day indeed 🙂

    Since you want a Hyperlink you can ignore most of my post. Keep it simple and put your Concatenate  into a HYPERLINK formula.

    https://support.microsoft.com/en-us/office/hyperlink-function-333c7ce6-c5ae-4164-9c47-7de9b76f577f

    HYPERLINK(link_location, [friendly_name])

    =HYPERLINK(your Concatenate formula, your Concatenate formula without the path ) 

    tada!!!

Resources