Forum Discussion
How to link multiple excel cells to external file
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
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 🙂
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!!!
