Extract file name & path from hyperlink

[mcs51mc]

Hi there,

Attached three "Slave" xls files and one "Master" xls file.

All slave files contains data at specific locations.

Column E of the master contains hyperlink to some slave files.

Column F of the master contains a formula to get the maximum value of column C of the slave file linked to in the same row at column E.
The formula is :
=MAX('C:\[Slave1.xls]Sheet1'!$C$3:$C$33000)

Column H of the master contains a formula to calculate the quotient of two cells the slave file linked to in the same row at column E.
The formula is : ='C:\Slave1.xls'!rngTeller/'C:\Slave1.xls'!rngNoemer


So far so good
But when I change any hyperlink in column E I want the data in column F and H to change according the data in the new slave file.

In other words : How to change the red parts automatically in the above formulas according to the new hyperlink.

Thanks a lot...

[NBVC]

You can use the Indirect() function for this... but, unfortunately, it only works if your slave files are open...

Fortunately, there is a free addin that you can install that improves this function so that it can work with closed workbooks. The addin is called Morefunc.dll and can be gotten here: http://xcell05.free.fr/english/

After you've downloaded the adding..

try:

Please Login or Register  to view this content.

copied down.

Please Login or Register  to view this content.

Note: If you will work with open workbooks, then take out the .ext from the Indirect functions....no need to install the addin.

[ravishankar]

Hi
The attached master.zip contains a macro which inserts formula in col F & H based on filename of the hyperlink in col E. It starts from row 4 (col E)
Ravi

[mcs51mc]

Hi,

Many thanks to both of you, both ways are ok
I go for the macro solution

Regards