Index Match Match total

mgerving

New Member
Joined
Mar 18, 2010
Messages
33
I have a workbook with 3 tabs, one is the list for the Index match (months), one is a list to total based on Data Val cells E2 & E3.
Is there a way to select, from Sheet 1, September and Week 1, grab the totals from E6 to E9, and paste them to the Blend tab cells B4 to B7, and then go back to Sheet 1 tab, choose week 2 and do the same, copy E6 to E9 on month and paste to C4 to C7 on Blend tab and still retain the '1' values?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

RustEE2020

New Member
Joined
Feb 21, 2020
Messages
30
Office Version
  1. 2016
Can you post an example of the worksheets you are working with so we can get a better idea of what you are trying to do?
You can put dummy data in it if need be.
 

mgerving

New Member
Joined
Mar 18, 2010
Messages
33
Try this using xl2bb
Book100.xlsx
ABCDE
2MonthSept
3Week1
4
5BaseCom 1Com 2Total
6Joe1005075225
7Tom2002530255
8Sally300110200610
9Betty40017525600
Sheet1
Cell Formulas
RangeFormula
B6B6=INDEX(Months!B3:E6,MATCH($E$2,Month,0),MATCH($E$3,Person,0))
B7B7=INDEX(Months!B10:E13,MATCH($E$2,Month,0),MATCH($E$3,Person,0))
B8B8=INDEX(Months!B17:E20,MATCH($E$2,Month,0),MATCH($E$3,Person,0))
B9B9=INDEX(Months!B24:E27,MATCH($E$2,Month,0),MATCH($E$3,Person,0))
E6:E9E6=SUM(B6:D6)
Named Ranges
NameRefers ToCells
Month=Months!$A$3:$A$6B6:B9
Person=Months!$B$2:$E$2B6:B9
Cells with Data Validation
CellAllowCriteria
E2List=Month
E3:E4List=Person


Week
1234
Joevalue from Sheet 1, Joe_Total (225)value from Sheet 1, Joe_Total (625)value from Sheet 1, Joe_Total (1025)value from Sheet 1, Joe_Total (525)AVERAGE(B4:E4)
Tom
Sally
Betty
 

RustEE2020

New Member
Joined
Feb 21, 2020
Messages
30
Office Version
  1. 2016

ADVERTISEMENT

Do you have another sheet called Months? Us that where you data is stored? What exactly are you trying to do, if you just want the data to be shown like in the last example you can add a weekly number to your data on months then use indexes/matches to pull the data for the week number at the top of that column.
 

mgerving

New Member
Joined
Mar 18, 2010
Messages
33
yes, see below.
Book100.xlsx
ABCDE
1Joe
21234
3Sept100500900400
4October200600100500
5November300700200600
6December400800300700
7
8Tom
91234
10Sept200600100500
11October300700200600
12November400800300700
13December500900400800
14
15Sally
161234
17Sept300700200600
18October400800300700
19November500900400800
20December600100500900
21
22Betty
231234
24Sept400800300700
25October500900400800
26November600100500900
27December700200600100
Months


What i am stuck on is how to capture the totals from E6 to E9 on Sheet 1, paste them into the Blend sheet in B4 to B7, go back to sheet 1, change the Weeks to '2' capture the values from E6 to E9, and paste those values into Blend sheet C4 to C7, and so on for '3' and '4' from Sheet 1.
 

mgerving

New Member
Joined
Mar 18, 2010
Messages
33

ADVERTISEMENT

Is there anyone else that might be able to help with my question?
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,416
Glad to hear you figured it out.

Do you mind posting your solution? Then it is perfectly fine to mark your post as the solution to help future readers.

Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,177
Messages
5,640,620
Members
417,158
Latest member
jimmy1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top