Pull data from others sheets using drop down list

0
votes

I have two sheets, in sheet2 I store all the data for each month. In sheet1 I want to do the calculaions for each month using many duffrent formula. In Sheet1 I created a drop down list that takes the months names from Sheet2. What I want to do is when I select for example January, January numbers will show and the calculations will be done for only January. Here is a capture of Sheet 2 and Sheet 1:

Sheet2 show the data (this is an example)

Sheet1 where I selec a month and calculations will be done for that month (this is an example)

I found something using VLOOKUP AND INDIRECT but it assign the selectd item from select list as a sheet name.

=VLOOKUP(B6,INDIRECT(""&$C$1&"!b2:c11"),2,0)

In the list items I have the months from Sheet2.. List items from Sheet2

Is their a possiable way to do that? because I am lost.

1
excel

1 Answers

0
votes

I think SUMPRODUCT() formula will meet your requirement. So, when you selct any month from dropdown list then SUMPRODUCT() formula will catch related value from sheet2 for that month and particulars. Then you can do further calculation with that value like 15% plus, minus blah blah. Here is formula to get value from sheet2 to sheet1 for selected month...

=SUMPRODUCT((Sheet2!$B$9:$E$9=$C$9)*(Sheet2!$A$10:$A$13=$A10)*(Sheet2!$B$10:$E$13))

enter image description here

INDEX(), MATCH() will also work in this case.

=INDEX(Sheet2!$B$10:$E$13,MATCH($A10,Sheet2!$A$10:$A$13,0),MATCH($C$9,Sheet2!$B$9:$E$9,0))