How can you make a drop down list effect the value returned in a seperate cell?

Hi,

I'm quite green when it comes to Excel so this may be a simple problem but I can't figure it out. Any help therefore would be much obliged.

I have seperate data sets in seperate sheets as putting them all in one sheet would mean an overload of information and it would also create an error in some of my LOOKUP formulas. On each sheet I have the same equations that output the desired values I require in the same cell in each sheet.

In order to target the data in each sheet I have created two drop down menus that correspond to the section and sub section outlined in each sheet i.e. If I selected the values of "HBS" and "1100" from each drop down menu respectively this would correspond to the sheet "HBS 1100".

I want to return the value of E2 along with several others from the sheet that is identified with the drop down menus in the main sheet. This my main problem as I cannot figure out how to do this with my limited knowledge of Excel, short of using hundreds of nested IFs which would be too cumbersome.

My second concern is; would it be easier if I combined the two drop down menus, although this would create a much longer list I think it would still be manageable?


Also I'm restricted to use of Excel 2003.

Thanks for the help in advance.

Which cells are the drop-downs in? Assuming A1 and B1, then you could do this:

=INDIRECT("'"&A1&" "&B1&"'!E2")

Hope this helps.

Pete

Originally Posted by Pete_UK

Which cells are the drop-downs in? Assuming A1 and B1, then you could do this:

=INDIRECT("'"&A1&" "&B1&"'!E2")

Hope this helps.

Pete

Works a treat.

Thankyou.