Sumif across multiple tabs

[colinheslop1984]

I want to create a working document which will compare sales data LFL

Multiple sets of data needs to be imported on a weekly basis on separate tabs, i.e WK01, WK02, WK01LY, WK02LY, etc. The layout of these sheets will always be identical.

Rather than having to repeat formula's 52 times on separate sheets, I would like a master sheet that will just let me choose which 2 tabs I want to compare data. Purely for simplicity, the data sheets will be laid out the same as below, minus the 'VS LY' column, its this column I would like to calculate. I'm thinking I would use data validation to insert a list of the tab names (in cell G1) then do some form of sumif formula? but not sure where I would go from there. Help please.

[TABLE="width: 500"]
<tbody>[TR]
[TD]CATEGORY[/TD]
[TD]UNITS[/TD]
[TD]VS LY[/TD]
[TD]SALES[/TD]
[/TR]
[TR]
[TD]DENIM[/TD]
[TD]B2[/TD]
[TD]C2[/TD]
[TD]D2[/TD]
[/TR]
[TR]
[TD]DRESSES[/TD]
[TD]B3[/TD]
[TD]C3[/TD]
[TD]D3[/TD]
[/TR]
[TR]
[TD]ETC[/TD]
[TD]B4[/TD]
[TD]C4[/TD]
[TD]D4[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi,

You really shared a minimum on data to go on so i can't do much more than give you some direction. Basically your OP contains 2 parts:
1. create a list of sheets used in the workbook and apply Data Validation to them so you can use it as a selecting field for the tabs to get the data from;
2. how to use the sheetname in the SUMIF function to collect the data from the sheet choosen;

For purpose of simplicity i'm assuming your comparing data on a weekly basis.

see if this helps:

Book1
	A	B	C	D	E	F	G
1	CATEGORY	UNITS	VS LY	SALES	VS LY		WK01
2	DENIM	4593	3539	114825	88475
3	DRESSES	6002	2188	150050	54700
4	ETC	9799	8279	244975	206975
recap

Cell Formulas
Range	Formula
B2	=SUMIF(INDIRECT("'"&$G$1&"'!A:A"),recap!A2,INDIRECT("'"&$G$1&"'!b:b"))
C2	=B2-SUMIF(INDIRECT("'"&$G$1&"LY'!A:A"),recap!A2,INDIRECT("'"&$G$1&"LY'!b:b"))
D2	=SUMIF(INDIRECT("'"&$G$1&"'!A:A"),recap!A2,INDIRECT("'"&$G$1&"'!c:c"))
E2	=D2-SUMIF(INDIRECT("'"&$G$1&"LY'!A:A"),recap!A2,INDIRECT("'"&$G$1&"LY'!c:c"))

 

[ravishankar]

Insert a sheet and name it as Summary and try the following macro codes

Sub yyy()
Cells(1, 26) = "sheetnames"
For a = 1 To Sheets.Count
If Sheets(a).Name <> "summary" Then
Cells(a, 26) = Sheets(a).Name
End If
Next a


x = Sheets("summary").Cells(Rows.Count, 26).End(xlUp).Row
Range("Z2:Z" & x).Select
ActiveWorkbook.Names.Add Name:="weeks", RefersToR1C1:= _
"=summary!R2C26:R" & x & "C26"
Range("G1:G2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=weeks"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
MsgBox "complete"
End Sub

on running, it lists the sheet names in column Z and creates a drop down in G1 and G2. You may choose the sheets to be compared and write sumif formulae suitably.
Ravi shankar