Look up Values from other file based on combo box's option

[Jac]

Hi,

I've a file (Book1.xls) with a list of items placed in a combo box and there
are all together 9 items, for example, AAA, BBB, CCC, DDD, EEE, FFF, GGG,
HHH, III. In this file also, I need to display the monthly data regarding
each item and those data are stored in another file in table format
(Book2.xls). All of the data are placed in different sheet according to
respective item range from cell A5:T12, which mean data of AAA item is in AAA
sheet in Book2.xls.

I would like the table data in Book1.xls change accordingly when the option
in combox box cahnged by looking up the data in respective sheet in
Book2.xls. Previously, I've tried out using IF Function but the nested level
can only go up to 7 layers; so, there is still has one option being left
out!!! After that, I did try out using Hlookup Function but it made the
things work even tidious cause there is no "decision-making" feature embedded
in this function; and whenever the option in combo box changed, it would
cause errors once the data couldn't be found / matched!!!

Thus, is there any other way to handle this problem???
Or macro is needed to be created???
Anyone out there could advise????

Thanking in advance.

 

[paul]

The indirect function will return the cells referenced by a text string.You
can build a text string to return the cell reference you need.Ie say your
combo box returns its sheet reference(aa,bbb,etc )in cell A1.In cell A2 you
have a formula ="[book2]"&a1&"!" and in cell a5 you have the formula
=indirect($A$2&"A5").As you change your combobox the cell a5 changes to
reflect the data in cell a5 of your respective sheets in book2...You complete
your table by changing the text A5 to B5 etc etc.as you copy down and across
For the indirect function to work book2 must be open.There may be a way to
complete the formula so hat the cell ref A5,B5 etc auto increment as you copy
the formula across and down your table.

 

[Dave Peterson]

You have a few choices.

1. You can build a formula that combines all that information and drop it into
a cell, then pick up that value from the cell.

='C:\My Documents\Excel\[book1.xls]Sheet1'!$A$1

(You could use a cell on a hidden worksheet if you want.)

2. You could retrieve values from a closed workbook using a routine from John
Walkenbach:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

3. You could use a formula that uses an addin from Laurent Longre
(morefunc.xll):
http://xcell05.free.fr/

That includes =indirect.ext()

====
But if you use excel's =indirect(), then the sending workbook has to be
open--else you'll get an error.