Reading Data from another workbook... depending on variable in a cell?

I'm hoping this is possible (if it is, not sure how easy it is!), and I'm really hoping that someone can help!

I have some workbooks that contain information regarding clients details, status, yearly spend, etc - general information. Each workbook is saved as the particular client's customer code/number - eg. ABC Company Pty will be ABC.xls, whereas XYZ Corporation would be XYZ.xls

What I want to do is this - have a separate "summary" workbook that summarises different bits of information in each of the client workbooks. For example, lists the client's current spend level.

I know I can put in a formula in the workbook referring to "=[ABC.xls]Sheet1!$B$1" to return the value in B1 on ABC.xls.

What I need is for the system to read the client code on the summary workbook and return the value on the workbook that refers to that client code.

That is, if I enter "ABC" into Cell A2 of the summary workbook, have the system return the value from "[????.xls]Sheet1!$B$1" where "????" is the value in Cell A2... does this make sense to anyone but me?

Hopefully so - if I've confused the issue let me know and I'll try to explain again... otherwise, can anyone help?

Cheers,

Rob.
(aka "Tired and Frustrated")

Rob

What you need is the INDIRECT function. Assuming the client code (and
workbook name) is in cell A2 of the summary sheet, then your formula
is:

=INDIRECT("'["&A2&"]Sheet1'!B1")

Beware, it will often need the source workbooks to be open, otherwise
you will get an error value.

HTH
Kostis Vezerides

Kostis,

Thanks HEAPS for that - if I'd known it was that simple I would have searched the Excel Help - cheers!

And while I'm here - here's to a HUGE THANK YOU to everyone that posts formulae, coding, scripting, etc to the questions on this site. You've helped me (and plenty of others) a number of times - you're all champions!

Cheers,

Rob.

Kostis,

Thanks HEAPS for that - if I'd known it was that simple I would have searched the Excel Help - cheers!

And while I'm here - here's to a HUGE THANK YOU to everyone that posts formulae, coding, scripting, etc to the questions on this site. You've helped me (and plenty of others) a number of times - you're all champions!

Cheers,

Rob.

Rob

glad to be of help.

Kostis