Excel VBA help for formula calculations - based on moving sumif criteria - Long Q.

[Ourkid1010]

Hi all,

So I have the following problem - I'll try and be as succinct and clear as possible:

a) I have a spreadsheet with about 300 products down in one column, so each row has 1 product. I have approximately 100 shops

b) Across the columns I have Periods and Weeks from P1W1 to P7W4 (so each period has 4/5 weeks)

c) At the top I have toggle filters for shop number, and by measure.

d) I have a seperate tab with data on it, with a string column which pulls together product code, shop number, week number and measure. The measure is filtered on using an indirect, which is pulling through the col based on which filter is used.

The underlying data takes the form:

Shop	PW	Product	Sales	Cost	Qty	Margin	String
1	P1W1	b	12	8	3	4	b1P1W1
2	P1W2	e	32	22	16	10

<tbody>
</tbody>

e) My sumif formulas are then of the form below (as an example), with Data! referencing my data tab, and criterias as per table below

	A	B	C	D	E	F	G
1	Shop#	3	Measure	Sales
2	Product	Week1	Week2	Week3	Week4	Week5
3	a	=sumif('Data!'H:H,$A3&$B$1&B$2&$D$1,'Data'!D:D)
4	b
5	c
6	d
7	e
8	f

<tbody>
</tbody>

f) As I drag the formula across the weeks, it will pull through data for the measure in $D$1, for the shop in $B$2, for Products in col $A, filtered by week in row 2. Doing so makes the sumif criteria a dynamic moving criteria pulling through lots of different ranges.

The problem is, with this type of referencing, I end up having a sheet that has far too many calcs, and takes about 12 minutes to calculate if i change one of the filters, as it then needs to recalc all the sumifs. My pc is by no means a dinosaur either, but the underlying data has 800,000 lines, at a product level

I wanted to solve this using VBA, as I believe this would be significantly faster, so that I could have a button on my sheet which when the criteria changed, would then run the calcs based on these differing criterias, but I've so far been unable to get a working solution to bring back any data at all.

For example, I could have buttons linked to a string of code that pulls through a different column based on the measure.

Here is what I have code wise so far - my logic has been to create the string in each cell, then use VBA to then take the string as it is in the cell, do the sumif on the data tab, then overwrite the string with the value it pulls back:

Option Explicit

Sub Formula()

Dim rRange As Range

Dim Summary As Range

Set Summary = Sheets("Product Type by Week").Range("G8:AJ607") 'where I've set where I want my formulas to work

Dim Data1 As Range

Set Data1 = Sheets("Data").Range("$i:$i")

Dim Data2 As Range

Set Data2 = Sheets("Data").Range("$E:$E")

Summary = Application.Sumif(Data1, "Activecell (" & ActiveCell.Address & ") = " & ActiveCell.Value, Data2)

End Sub

Seriously difficult, as I need this dynamic tool to be able to quick sort or filter between different shops, measures and weeks quickly, to allow side by side trend analysis in a centralised place...

Any ideas would be much appreciated.

Many thanks for reading!

 

[Ourkid1010]

Sorry for bumping but *Bump*
Many thanks in advance for any help

 

[Ourkid1010]

Need to bump up top; any help or even thoughts would be appreciated!