Forum Discussion
INDEX & MATCH with SUMPRODUCT across multiple sheets
That's as
=INDEX(Sales!$A$1:$M$11,MATCH($A2,Sales!$A:$A,0),MATCH(B$1,Sales!$1:$1,0)) + INDEX(Referrals!$A$1:$M$11,MATCH($A2,Referrals!$A:$A,0),MATCH(B$1,Referrals!$1:$1,0))
What "dynamic drop down list"? There are no formulae in your sheet other than the ones that generate random numbers in "Sales" and "Referrals". Why not just add the following in B2 in "Totals"?
=Sales!B2+Referrals!B2..... and drag it down and across. Otherwise, please clarify and indicate which value you would want to see where and why.
Hello and thank you for your response.
As this is an example i can only explain what i'm trying to do. My actual worksheet has 5 or 6 tabs and one main tab in which there are various items where the total is needed for different segments based on the month selected in the list. That total is drawn from combining the correlating segments from the other tabs all into one cell in the main dashboard tab.Riny_van_Eekelen
My thought is it would be some combinations of an INDEX and MATCH formula with SUMPRODUCT, but i could be wrong
shade206 Everybody is right until proven wrong!
But, it's difficult to see what you want without and example that is close(r) to your actual situation. You talk about a drop down list, but it's not in the file you uploaded. Your main tab probably does not look the same as the other ones. So, unless you can upload a more realistic example, I fear that you will not get much help here. Of course, I may be wrong.
You are absolutely right! I uploaded an incomplete sample 🙂
That has now been corrected, maybe now we can resolve? Riny_van_EekelenIf there are only two sheets to sum that could be simply (INDEX/MATCH on Sales) + (INDEX/MATCH on Referral)