Sumif across multiple tabs

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
102
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.

CATEGORYUNITSVS LYSALES
DENIMB2C2D2
DRESSESB3C3D3
ETCB4C4D4

<tbody>
</tbody>
 

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,263
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:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">CATEGORY</td><td style=";">UNITS</td><td style=";">VS LY</td><td style=";">SALES</td><td style=";">VS LY</td><td style="text-align: right;;"></td><td style=";">WK01</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">DENIM</td><td style="text-align: right;;">4593</td><td style="text-align: right;;">3539</td><td style="text-align: right;;">114825</td><td style="text-align: right;;">88475</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">DRESSES</td><td style="text-align: right;;">6002</td><td style="text-align: right;;">2188</td><td style="text-align: right;;">150050</td><td style="text-align: right;;">54700</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">ETC</td><td style="text-align: right;;">9799</td><td style="text-align: right;;">8279</td><td style="text-align: right;;">244975</td><td style="text-align: right;;">206975</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">recap</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=SUMIF(<font color="Blue">INDIRECT(<font color="Red">"'"&$G$1&"'!A:A"</font>),recap!A2,INDIRECT(<font color="Red">"'"&$G$1&"'!b:b"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=B2-SUMIF(<font color="Blue">INDIRECT(<font color="Red">"'"&$G$1&"LY'!A:A"</font>),recap!A2,INDIRECT(<font color="Red">"'"&$G$1&"LY'!b:b"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=SUMIF(<font color="Blue">INDIRECT(<font color="Red">"'"&$G$1&"'!A:A"</font>),recap!A2,INDIRECT(<font color="Red">"'"&$G$1&"'!c:c"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=D2-SUMIF(<font color="Blue">INDIRECT(<font color="Red">"'"&$G$1&"LY'!A:A"</font>),recap!A2,INDIRECT(<font color="Red">"'"&$G$1&"LY'!c:c"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top