Attempting to insert multiple thousands of Hyperlinks to corresponding folders

Hi all,

I am currently attempting to insert hyperlinks into a relatively large Excel spread sheet that link to corresponding folders.

Eg. Cell A3 contains text "3000"
I would like this cell to contain a hyperlink to folder "C:\Folder\subFolder\3000" for example.

This is obviously easy enough but my A column is currently at A3839 and I would like to insert Hyperlinks to each of the corresponding sub folders between A3 and A3839 (and continuing)

Is there a straight forward way of completing this task?

Is a Macro the best way forward?

Hi
this is very easy with a macro. Just to clear up:

• you want your hyperlink to open a folder, not a file in the folder
• the names of the folders you want to link to are the contents of cells A3:A3839
• you want to overwrite the contents of the cells in column A containing folder names with hyperlinks to those folders

cheers
Nicky

Hi all, thanks for the quick response.

yes, to clear up i want the hyperlinks to open the sub folders only, not specific files. In addtition the cell content is not exactly the same as the corresponding file. Each entry in Collumn A has the prefix NNNN. Im assuming this will need to be accounted for...

Ideally i would like the contents on the cells in collumn A to stay the same as they are now.

follow up:

i would like the cells to still display NNNN 3000 however link to the corresponding "3000" folder

Hi

in that case try Jakobshavn's solution

in a free column type the formula

Please Login or Register  to view this content.

and copy down into as may rows as needed

Ok, great. I can see that this will work, however it doesnt appear to be linking to the specified folder currently.

=HYPERLINK("C:\Folder\SubFolders\NNNN 3000s"&A3) is what i have entered into the newly avalible B column
Cell A3 = "NNNN 3000"
Inside folder "NNNN 3000s" are a further 100 folders all titled "3000", "3001" etc
These are the folders that i would like to link to A3, A4 respectively.

Are we still on the right track?
Sorry if this new information complicates things.

Really appreciating your help thus far.

the issue is identifying the names of the file or folders you want to link to. If all your folders are in subdirectory C:\Folder\subFolder\NNNN 3000s\, the folder names start at 3000 and are listed in column A beginning in A3, try this:

Please Login or Register  to view this content.

If not all of the folders are subdirectories of the same folder, we will have to tweak the formula

I am having the same issue with this formula. I believe the issue lies with the &A3 section of the formula. Am i right in assuming that adding *A3 at the end will essentially make the link into =HYPERLINK("C:\Folder\subFolder\NNNN 3000s\NNNN 3000)

The problem with that is no such folder exists as the folders do not have the NNNN prefix that is present in the A column.

For the current formula to work the source should end up looking like this:
=HYPERLINK("C:\Folder\subFolder\NNNN 3000s\3000)
However using A3 will not have the desired effect due to the fact that A3= "NNNN 3000" and not "3000"

Am i right in thinking that the formula needs to take this prefix into accound and somehow ignore it when it is creating the respective hyperlinks?

Hi

can you show is exactly what is in cell A3 and what is the full path of the folder you want to link to?

in combination, the text in your formula and the text in the cell you link to should, when combined, make the full path of the folder you want to link to.

eg if the folder name is:

C:\Folder\subFolder\NNNN 3000s\3001

then combining the path

C:\Folder\subFolder\NNNN 3000s\

with the folder name

3001

will create an address the hyperlink formula can take you to.

Certainly.

The Full path for one specific folder is C:\Folder\subFolder\NNNN 3000s\3001
The content of the corresponding cell is NNNN 3001

So somehow the Hyperlink formula needs to ignore the cells prefix of NNNN and just use the 3001 part

ok
if all of your cells in column A contain "NNNN" before the folder name, try this:

Please Login or Register  to view this content.

You bloody Rippa!

Thank you so much for your time Nicky. Really appreciate the assist. I am relatively new to excel and i am only just beginning to scratch the surface of its capabilities.
People like yourselves make that process a whole lot smoother.

Thanks again.