Hyperlink text from linked workbook

[Albert, Norway]

I have a seperat workbook for each of my clients with the clientno as the
filename i.e. 1001.xlsm, 1002.xlsm etc. All these files are in lets say
c:/docs. First sheet in each clientworkbook is "Factsheet". In each factsheet
A1 states the clientname.

In the same directory I have my mastersheet.
In the first column I have the client numbers.
In the second column I want to have the hyperlinkformulas that, based on the
value in 1. column, makes the link to the relevant clientworkbook AND with
the clientname picked up from the linked workbook Factsheet!A1 as the linktext

I have two formulas that each does half of the job:
=HYPERLINK("C:\Docs\"&$a2&".xlsm";a2)
In this case the hyperlink works and opens the relevant file, but the
hyperlinktest is not the client name from the Factsheet

=HYPERLINK("C:\Docs\"&$a2&".xlsm";'C:\Docs\[1001.xlsm]Factsheet'!$a$1)

In this formula I get the clientname from the 1001.xlsm's factsheets cell
A1, but the hyperlink does not work AND the filename in the formula is not a
reference to the clientnumber in the first column.

Can anybody please help me with a formula that does both the tricks?
Thank you very much in advance.

 

[Héctor Miguel]

hi, Albert !

I guess your best bet is to use vba:

- in '_change' event for the cells where you put the client number (column 1 ?)
- to read from closed workbooks and write the content (i.e.) in column 2 ?
- after that, you could use column 2 as the hyperlink-text function

one question: do you use to modify several cells in column 1 at one time ?

regards,
hector.

__ OP __