Hyperlink Problems

[sandyl]

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]

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]

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/[email protected]

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]

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/[email protected]

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]

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]

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

Harlan did put instructions in his post.

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