Variable link data

[PSM]

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 ???

 

[~L]

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\C48\["&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]

Thanks for your post.

Due to our company IT rules we do not allow any third party software i
our system so I'm hoping for a answer from someone smarter and wise
than me within Excel as I cannot think of a way.

Finger crossed

 

[David Biddulph]

If you don't quote enough of the previous message to put your reply into
conext, we don't know what you're asking.

 

[PSM]

Sorry David, bit new to this sort of thing. I didn;t wan to repeat th
thread above so deleted it out. I've added to below if this helps. An
help would be appreciated.

If you don't quote enough of the previous message to put your reply int

conext, we don't know what you're asking.
--
David Biddulph

"PSM" (e-mail address removed) wrote in message

Thanks for your post.

Due to our company IT rules we do not allow any third party softwar
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.

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

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

Because Indirect is volatile, the results will only display if th
sheet in
question is open. You can hyperlink to the file using
=hyperlink("'\\company.local\Server\Office\Cpacks\C48\"&A2&".xls") t
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 t
do
what indirect does only with closed workbooks. I'm not sure how i
will
perform with 2000 workbooks since, as I have seen it explained, i
opens a
hidden copy of the reference workbook.

:
-

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 ???

 

[~L]

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.

 

[David Biddulph]

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

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.

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" (e-mail address removed) 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.

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\C48\["&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.

:
-

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. I've tried and tested your solution and it works
treat.

Thanks again for your help.

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, cop
the
values, reformat the cells in the destination to 'general' instead o
text,
then paste the values back in.

:
-

Thanks for your post.

Due to our company IT rules we do not allow any third party softwar
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.