newbie query excel 2010

[excelsupport55]

Hi All,

I'm a newbie to the forum and have limited a understanding of Excel. Currently using excel 2010.

My question is around totalling columns across multiple workbooks. Let me break it down more sizeable and understandable format.

1. Data source is in workbooks with names such as ( 2015 Jan,2015 Feb,2015 Mar,2015 Apr,2015 May.... )
2. The data i'm interested is found in the sheet (ProdA) which is common across all the workbooks.
3. The rows that i need are as follows:
   1. G4:G7("storeA" is the column heading) , H4:H7("storeB" is the column heading), J4:J7("StoreD" is the column heading) separately totalled under. This should be totalled under "WA" Ex:
   2. [TABLE="width: 500"]
      <tbody>[TR]
      [TD][/TD]
      [TD]storeA[/TD]
      [TD]storeB[/TD]
      [TD]storeC[/TD]
      [/TR]
      [TR]
      [TD]agent1[/TD]
      [TD]2[/TD]
      [TD]3[/TD]
      [TD]4[/TD]
      [/TR]
      [TR]
      [TD]agent2[/TD]
      [TD]1[/TD]
      [TD]2[/TD]
      [TD]3[/TD]
      [/TR]
      [TR]
      [TD]agent3[/TD]
      [TD]5[/TD]
      [TD]6[/TD]
      [TD]7[/TD]
      [/TR]
      [TR]
      [TD]agent4[/TD]
      [TD]8[/TD]
      [TD]9[/TD]
      [TD]1[/TD]
      [/TR]
      [TR]
      [TD]WA Total[/TD]
      [TD]xx[/TD]
      [TD]yy[/TD]
      [TD]zz[/TD]
      [/TR]
      </tbody>[/TABLE]
   3. And i need the SA total the same way. The rows are different, which looks like G8:12,H8:12,J8:12[TABLE="width: 500"]
      <tbody>[TR]
      [TD][/TD]
      [TD]storeA[/TD]
      [TD]storeB[/TD]
      [TD]storeC[/TD]
      [/TR]
      [TR]
      [TD]agent1[/TD]
      [TD]1[/TD]
      [TD]2[/TD]
      [TD]3[/TD]
      [/TR]
      [TR]
      [TD]agent2[/TD]
      [TD]4[/TD]
      [TD]5[/TD]
      [TD]6[/TD]
      [/TR]
      [TR]
      [TD]agent3[/TD]
      [TD]7[/TD]
      [TD]8[/TD]
      [TD]9[/TD]
      [/TR]
      [TR]
      [TD]agent4[/TD]
      [TD]1[/TD]
      [TD]2[/TD]
      [TD]3[/TD]
      [/TR]
      [TR]
      [TD]agent5[/TD]
      [TD]4[/TD]
      [TD]5[/TD]
      [TD]6[/TD]
      [/TR]
      [TR]
      [TD]SA Total[/TD]
      [TD]aa[/TD]
      [TD]bb[/TD]
      [TD]cc[/TD]
      [/TR]
      </tbody>[/TABLE]
   4. Now what i need is, something like the following 2 tables:[TABLE="width: 500"]
      <tbody>[TR]
      [TD]Month[/TD]
      [TD]storeA[/TD]
      [TD]storeB[/TD]
      [TD]storeC[/TD]
      [/TR]
      [TR]
      [TD]JAN[/TD]
      [TD]Tot WA sales for JAN from the previous working[/TD]
      [TD][/TD]
      [TD][/TD]
      [/TR]
      [TR]
      [TD]FEB[/TD]
      [TD][/TD]
      [TD][/TD]
      [TD][/TD]
      [/TR]
      [TR]
      [TD]MAR[/TD]
      [TD][/TD]
      [TD][/TD]
      [TD][/TD]
      [/TR]
      </tbody>[/TABLE]
      
      
      
      [TABLE="width: 500"]
      <tbody>[TR]
      [TD]Month[/TD]
      [TD]storeA[/TD]
      [TD]storeB[/TD]
      [TD]storeC[/TD]
      [/TR]
      [TR]
      [TD]JAN[/TD]
      [TD]Tot SA sales for JAN from the previous working[/TD]
      [TD][/TD]
      [TD][/TD]
      [/TR]
      [TR]
      [TD]FEB[/TD]
      [TD][/TD]
      [TD][/TD]
      [TD][/TD]
      [/TR]
      [TR]
      [TD]MAR[/TD]
      [TD][/TD]
      [TD][/TD]
      [TD][/TD]
      [/TR]
      </tbody>[/TABLE]
      
      
      Wow pretty serious stuff for me. May be not for the veterans. Hoping to get some help you you to nail it.
      
      Cheers
      OSP

 

[123rickfear]

A vlookup with a match should do it. something like this to give you the SA Total for Store A for Jan, then drag it along.

for Feb you would need to change the word Jan for Feb (either manually or by a find/ replace), and so on.

=VLOOKUP("SA Total",'Jan 2015.xlsxProd A'!$A:$D,MATCH(B$1,'Jan 2015.xlsxProd A'!$A$1:$D$1,0))

there will no doubt be a fancier way of using an indirect so you don't need to change the word Jan to Feb, but it's not something I can seem to get working.

Rick

 

[excelsupport55]

A vlookup with a match should do it. something like this to give you the SA Total for Store A for Jan, then drag it along.

for Feb you would need to change the word Jan for Feb (either manually or by a find/ replace), and so on.

=VLOOKUP("SA Total",'Jan 2015.xlsxProd A'!$A:$D,MATCH(B$1,'Jan 2015.xlsxProd A'!$A$1:$D$1,0))

there will no doubt be a fancier way of using an indirect so you don't need to change the word Jan to Feb, but it's not something I can seem to get working.

Rick

Thank you Rick. Let me try what you have suggested