drop down list

[tracy]

hi, i want to calculate the total price from excel according to the 3 drop
down list , for example, if the client from east(drop down list got east,
west, south)and the client type is 1 (drop down list got 1,2,3) and the
family size is 2( drop down list got 1,2,3.4,more) , it goto the east
spreadsheet and refer to the value type 1 ,size 2. and there are 3 different
worksheet(east, west ,south) and the data in each spreadsheet. Therefore, i
am wondering if i can auto fill in the cell which calculate the profit of the
client when i choose the list .

 

[Ed Ferrero]

Hi tracy,

Go to http://www.edferrero.com/ExcelCharts/tabid/102/Default.aspx and
download the 'Report Workbook' sample. This shows how to use some drop-downs
to provide parameters for formulas that access data on a different workbook
and worksheet.

Ed Ferrero
www.edferrero.com

 

[Jean-Guy]

Hi,

Still not sure what you're trying to do but let's assume you have a table in
each of the 3 sheets, you have a row header in column A (A2=1,A3=2 and A3=3)
and a column header in row 1 (B1=1,C1=2,D1=3 and E1=4) with different values
in B2:E4.

now let's assume your first drop down list is in A1 of the main sheet and
the second drop down list in B1 and the 3rd in C1 then you could use
something like this:

=INDEX(INDIRECT(A1&"!B2:E4"),MATCH(B1,INDIRECT(A1&"!A2:A4"),0),MATCH(C1,INDIRECT(A1&"!B1:E1"),0))

Not sure if this will help but it might guide you in the right direction!

Regards!
Jean-Guy