There are ways to do this, but if I'm reading you correctly there are 2000
other workbooks you're pulling data from?

One way is:
=indirect("'\\company.local\Server\Office\Cpacks\C 48\["&A2&".xls]Short'!$A$1")

Because Indirect is volatile, the results will only display if the sheet in
question is open. You can hyperlink to the file using
=hyperlink("'\\company.local\Server\Office\Cpacks\ C48\"&A2&".xls") to make
it easier to open

or you can download the add-in MoreFunc from:
http://xcell05.free.fr/morefunc/english/

This add-in has a function called Indirect.Exe which will allow you to do
what indirect does only with closed workbooks. I'm not sure how it will
perform with 2000 workbooks since, as I have seen it explained, it opens a
hidden copy of the reference workbook.

"PSM" wrote:


I have a spreadsheet which is basically a source of different file
contents.
In
Column A I have the file name (C4835)
Column B I have the variable path which is always the first three
characters of the file name (C48)
In another row I would like to do a link to show what appears in A1
(title) of each sheet.

I have 2000+ lines so would like to automate in some way the changing
of variable part of the file path and file name in each cell.

Examples
'\\company.local\Server\Office\Cpacks\C48\[C4835.xls]Short'!$A$1

'\\company.local\Server\Office\Cpacks\C51\[C5185.xls]Short'!$A$1

Any ideas please ???




--
PSM