# Sumif across multiple tabs

#### colinheslop1984

##### Board Regular
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.

 CATEGORY UNITS VS LY SALES DENIM B2 C2 D2 DRESSES B3 C3 D3 ETC B4 C4 D4

<tbody>
</tbody>

#### jorismoerings

##### Well-known Member
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:

#### ravishankar

##### Well-known Member
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
"=summary!R2C26:R" & x & "C26"
Range("G1:G2").Select
With Selection.Validation
.Delete
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

1,082,135
Messages
5,363,344
Members
400,729
Latest member
Lisa McConachy

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...