Forum Discussion
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 have worked out how to do this for each cell individually, but of course it would be time-consuming to go through each cell and link it to its relevant document. It is clear which document each cell needs to be linked to because of a number in both document and row of the relevant cell. I am looking for help on how to essentially 'tell' excel to search for and link each file with the same number to each cell in the column. is anyone able to help me with this?
Hi.
Assume your link looks something like this:
='C:\tmp\[doc0001.xlsx]Sheet1'!$A$1And 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
4 Replies
Hi.
Assume your link looks something like this:
='C:\tmp\[doc0001.xlsx]Sheet1'!$A$1And 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
- **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**
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 🙂
