I am trying to create a summary worksheet based on other worksheet
contents (that will be created in the future). I am looking to
automate this and am having difficulty auto referencing hyperlinks.
For example, I have a cell A1 with the folder path and cells a2...
have sequential numbers. I then create hyperlinks in b2... using these
references to make up the file name. What I then need to do is insert
information from the other worksheets into in cells c2..f2 etc. I want
to use cell references a1 and a2.. to create the hyperlink so that it
is easily updated if the filename or path change but cannot seem to do
this. I have tried various methods but am only currently stuck with
entering the entire filename and path. I am new to this so want to
avoid VB if possible.

Sandy L

sandyl wrote:
I am trying to create a summary worksheet based on other worksheet
contents (that will be created in the future). I am looking to
automate this and am having difficulty auto referencing hyperlinks.
For example, I have a cell A1 with the folder path and cells a2...
have sequential numbers. I then create hyperlinks in b2... using
these
references to make up the file name. What I then need to do is
insert
information from the other worksheets into in cells c2..f2 etc. I
want
to use cell references a1 and a2.. to create the hyperlink so that it
is easily updated if the filename or path change but cannot seem to
do
this. I have tried various methods but am only currently stuck with
entering the entire filename and path. I am new to this so want to
avoid VB if possible.

Sandy L

I'm sure there is someone out there who can help. This is quite urgent
and would appreciate any positive comments.

Sandy L

I'm not quite sure why you want to use hyperlinks. These will open the other
file--not retrieve values from it.

If the other files were open, you could build a formula that uses =indirect()
that points at the correct open workbook, worksheet and cell to return that
value.

But if the workbooks are closed =indirect() won't work.

But Harlan Grove wrote a userdefined function called =Pull() that will do what
you want.

http://www.google.com/groups?selm=sH...wsranger. com

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

sandyl wrote:

sandyl wrote:
I am trying to create a summary worksheet based on other worksheet
contents (that will be created in the future). I am looking to
automate this and am having difficulty auto referencing hyperlinks.
For example, I have a cell A1 with the folder path and cells a2...
have sequential numbers. I then create hyperlinks in b2... using
these
references to make up the file name. What I then need to do is
insert
information from the other worksheets into in cells c2..f2 etc. I
want
to use cell references a1 and a2.. to create the hyperlink so that it
is easily updated if the filename or path change but cannot seem to
do
this. I have tried various methods but am only currently stuck with
entering the entire filename and path. I am new to this so want to
avoid VB if possible.

Sandy L

I'm sure there is someone out there who can help. This is quite urgent
and would appreciate any positive comments.

Sandy L

--

Dave Peterson

Dave Peterson wrote:
I'm not quite sure why you want to use hyperlinks. These will open
the other
file--not retrieve values from it.

If the other files were open, you could build a formula that uses
=indirect()
that points at the correct open workbook, worksheet and cell to
return that
value.

But if the workbooks are closed =indirect() won't work.

But Harlan Grove wrote a userdefined function called =Pull() that
will do what
you want.


http://www.google.com/groups?selm=sH...wsranger. com

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

sandyl wrote:

sandyl wrote:
I am trying to create a summary worksheet based on other
worksheet
contents (that will be created in the future). I am looking to
automate this and am having difficulty auto referencing
hyperlinks.
For example, I have a cell A1 with the folder path and cells
a2...
have sequential numbers. I then create hyperlinks in b2... using
these
references to make up the file name. What I then need to do is
insert
information from the other worksheets into in cells c2..f2 etc.
I
want
to use cell references a1 and a2.. to create the hyperlink so
that it
is easily updated if the filename or path change but cannot seem
to
do
this. I have tried various methods but am only currently stuck
with
entering the entire filename and path. I am new to this so want
to
avoid VB if possible.

Sandy L

I'm sure there is someone out there who can help. This is quite
urgent
and would appreciate any positive comments.

Sandy L

--

Dave Peterson

Dave,

First, thanks for responding. Second, apologies but did not explain
correctly. Am looking to do the following:

=([$A$1&A6&".xls"]Sheet1!c$3$)

such that A1 contains the path and A6.....Z6 contain the name. I can
then easily copy the various formulas to following cells as I create
additional workbooks. Also accommodates any changes to the location.

You hinted about Harlan Grove's solution which will avoid opening all
workbooks so will look at that but if you could provide further advice
then it would be most gratefuly accepted.

Regards,

Sandy Lumsden

Try Harlan's function and post back if you have trouble.

Harlan did put instructions in his post.

Sandyl wrote:

Dave,

First, thanks for responding. Second, apologies but did not explain
correctly. Am looking to do the following:

=([$A$1&A6&".xls"]Sheet1!c$3$)

such that A1 contains the path and A6.....Z6 contain the name. I can
then easily copy the various formulas to following cells as I create
additional workbooks. Also accommodates any changes to the location.

You hinted about Harlan Grove's solution which will avoid opening all
workbooks so will look at that but if you could provide further advice
then it would be most gratefuly accepted.

Regards,

Sandy Lumsden

--

Dave Peterson