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

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

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

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

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

I was brain-dead yesterday. A much better way to do this:

="'\\company.local\Server\Office\Cpacks\C48\["&A2&".xls]Short'!$A$1"

Then copy, paste values should produce text of

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

Then use the find/replace tool (ctrl+H) to chane '\\ to ='\\.

It might get stuck thinking the cells are text. If that happens, copy the
values, reformat the cells in the destination to 'general' instead of text,
then paste the values back in.

"PSM" wrote:


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

No, nothing to add. I think the answer you've had pretty well sums it up.
--
David Biddulph

"PSM" wrote in message
...

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

Thanks for your post. I've tried and tested your solution and it works a
treat.

Thanks again for your help.

~L;3072028 Wrote:
I was brain-dead yesterday. A much better way to do this:

="'\\company.local\Server\Office\Cpacks\C48\["&A2&".xls]Short'!$A$1"

Then copy, paste values should produce text of

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

Then use the find/replace tool (ctrl+H) to chane '\\ to ='\\.

It might get stuck thinking the cells are text. If that happens, copy
the
values, reformat the cells in the destination to 'general' instead of
text,
then paste the values back in.

"PSM" wrote:
-

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
-




--
PSM