Sorry David, bit new to this sort of thing. I didn;t wan to repeat the
thread above so deleted it out. I've added to below if this helps. Any
help would be appreciated.
David Biddulph;3071750 Wrote:
If you don't quote enough of the previous message to put your reply into
conext, we don't know what you're asking.
--
David Biddulph
"PSM" wrote in message
...-
Thanks for your post.
Due to our company IT rules we do not allow any third party software
in
our system so I'm hoping for a answer from someone smarter and wiser
than me within Excel as I cannot think of a way.
Finger crossed.
--
PSM -
~L;3070493 Wrote:
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
-
--
PSM