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

Ourkid1010

New Member
Joined
Jan 16, 2014
Messages
4
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:

ShopPWProductSalesCostQtyMarginString
1P1W1b12834b1P1W1
2P1W2e32221610

<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


ABCDEFG
1Shop#3MeasureSales
2ProductWeek1Week2Week3
Week4Week5
3a=sumif('Data!'H:H,$A3&$B$1&B$2&$D$1,'Data'!D:D)
4b
5c
6d
7e
8f

<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!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top