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.