Jan 09 2020 10:54 AM - edited Jan 09 2020 12:54 PM
Hello All,
I've provided a brief sample. I need the formula to pull based on month in my dynamic drop down list the combined product of "Sales" and "Referrals" into "Totals" for the correlating cell.
i.e. cell B2 in "Totals" should be the SUM of B2 in "Sales" and B2 in "Referrals" (based on the month selection from the dynamic dropdown list)
What in SAM HILL TARNATION is going on?!?!
Jan 09 2020 11:33 AM
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.
Jan 09 2020 11:48 AM
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
Jan 09 2020 12:09 PM
@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.
Jan 09 2020 12:27 PM
If the arrangement is such where the values are always in the same spot in each sheet, you can use a 3D reference.
=SUM(Sales:Referrals!B2)
If the values are not in the same place in each sheet then an INDEX MATCH would not work.
Jan 09 2020 12:55 PM
You are absolutely right! I uploaded an incomplete sample :)
That has now been corrected, maybe now we can resolve? @Riny_van_Eekelen
Jan 09 2020 12:57 PM
Hmm, the 3D reference here would not work as the place it is sum'ing the values differs on each sheet. @Patrick2788
Jan 09 2020 01:03 PM
If there are only two sheets to sum that could be simply (INDEX/MATCH on Sales) + (INDEX/MATCH on Referral)
Jan 09 2020 01:07 PM
What would that formula look like? @Sergei Baklan
Jan 09 2020 01:25 PM
SolutionThat'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))
Jan 09 2020 03:07 PM - edited Jan 09 2020 03:08 PM
I can't get it to work in the actual sheet... here is an abbreviated version. Can you take a look? @Sergei Baklan
Jan 10 2020 01:26 AM - edited Jan 10 2020 01:49 AM
I'm glad you received much help since my previous post. Allow me to help you out now.
You are trying to index the a third row element (from E2:E4) in a range with only one row (H4:S4). Change the first one to E4:E4 or the second one to H2:H4 and it will work. But....... why not try to make it all be bit more dynamic AND easier to follow and maintain. Re-attaching your workbook with your formula re-written, using Named Ranges as I assume you want to be able to copy the formula down to cover more than one "Measured By".
Jan 09 2020 01:25 PM
SolutionThat'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))