Shared folder Directory in Excel

[Indranee]

Hai,

In our department we have a shared folder. I want to create the shared folder directory in excel. I tried and If I change the file location in the shared folder the directory in excel still showing the old location of the file.

Is there any other way to do this.

Thanks in advance.

Indranee

 

[Monty]

Hello Indranee.

Select a blank cell and type this formula =MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1)) (A1 is the cell that you want to extract the filename from), and press Enter button, then drag the fill handle to fill range you want. After that, the filename is extracted from each cell.

 

[Indranee]

Hello Monty,

I tried but cannot get it. There main folder, sub folder and sub of sub folder. Referring to the file I attached column D is Sub of sub folder with hyperlink to open the files in the shared folder.

The thing is when I change the files location the excel directory still showing the old location.

Please help.

Thanks in advance.

Indranee

 

[Indranee]

Hai,

I follow the above formula. Cannot get through. I open new excel sheet. Copy and paste the formula. Replacing A1 i put the folder name which i want to extract.

But not success.

Please help.

Thanks

 

[Chihiro]

What you are looking for cannot be completed with formula alone.
It will require VBA.

I'd suggest using add-in created by Chip Pearson.
http://www.cpearson.com/Excel/foldertree.aspx