different calculation based on dropdown list

[skykaiz]

Hello guys,

I have got a questions regarding different calculations based on the selection of the drop-down list. In other words, how can I have different calculations based on what variable I select in the drop-down-list?


For example, let's say I've got 5 companies and I created a drop-down list where I can select one company.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Company A[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[/TR]
[TR]
[TD]Company D[/TD]
[/TR]
[TR]
[TD]Company E[/TD]
[/TR]
</tbody>[/TABLE]

Then I got the data like sales per week and a variable total sales:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sales per week[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]21[/TD]
[TD]12[/TD]
[TD]31[/TD]
[TD]12[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]24[/TD]
[TD]26[/TD]
[TD]21[/TD]
[TD]26[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]12[/TD]
[TD]17[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Company D[/TD]
[TD]12[/TD]
[TD]11[/TD]
[TD]31[/TD]
[TD]27[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Company E[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]26[/TD]
[TD]15[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Total sales[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now I would like to calculate total sales, which is Sum(Mon:Fri), but based on what Company I select in the drop down list e.g. if I select Company A, it should calculate Sum(21:21), if I select Company B then Sum(24:11) and so on.


How can I make it happen in excel?

Thanks a lot!

 

[Fluff]

Maybe something like

Book1
	A	B	C	D	E	F	G	H	I	J
1	Sales per week	Mon	Tue	Wed	Thu	Fri
2	Company A	21	12	31	12	21				Company A
3	Company B	24	26	21	26	11				97
4	Company C	12	15	12	17	12
5	Company D	12	11	31	27	21
6	Company E	21	22	26	15	12
Report

Cell Formulas
Range	Formula
J3	=SUM(INDEX(B2:F6,MATCH(J2,A2:A6,0),0))