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

[Rob Moyle]

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")

 

[vezerid]

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

 

[Rob Moyle]

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 Moyle]

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.

 

[vezerid]

Rob

glad to be of help.

Kostis