Help with links

[RossMcColl]

Before starting my question I'd like to say that I'm of moderate skill with Excel, know most worksheet functions and those I don't know I can learn, however I know no VBA, so code is beyond me.

10 files in a folder, all named after people. All contain a value in cell A1.

I need a spreadsheet that contains a list of names, and depending on the names in the list will show the values from the corrosponding files.

I assume the links to the values would be ='c:\[Jim Smith.xls]Sheet1'!$A$1

is there any way of the 'Jim Smith' bit reading from the input on another cell?

Many thanks,

Ross

 

[Richard Schollar]

Hi Ross

You could use INDIRECT, but note that the target file must be open for this to work:

Name in A1 eg "Jim Smith":

=INDIRECT("'C:\[" & A1 & ".xls]Sheet1'!$A$1")

or you could download the morefunc add-in and then make use of the INDIRECT.EXT function if you want to return results when the Target is closed (see recommended downoloads message).

Best regards

Richard

 

[RossMcColl]

Thankyou Richard,

I wouldn't be able to have all the target files open, and the morefunc add-in isn't supported by our tech department at work so they won't let us have it in our department

 

[RossMcColl]

Hmm, would it be easier just to build a database in access?

 

[RossMcColl]

Anyone?

 

[GlennUK]

Hmm, would it be easier just to build a database in access?

In what way would you envisage that working, exactly?

As for doing it in Excel, Richard has given you 2 ways that don't suit your circumstances. Could you write a macro to build the link formulae by looking at cell contents, instead?

 

[RossMcColl]

Brilliant idea!

Erm, how would I use a macro to "build" and run a formula?

 

[RossMcColl]

Bump

 

[GlennUK]

I'd start by doing macro record of entering your formula:
='c:\[Jim Smith.xls]Sheet1'!$A$1
and then adjust the macro.

Let us know how you get on, and if you get stuck post the macro on here.