I get sent every week a raw data file which i import into excel - the w.e. tabs - and im using the SUMPRODUCT function to pull data from these raw data tabs into a summary table, so i can make a pivot table from it, and therefore a pivot chart, showing time on the x axis, and number of orders on the y axis.
At the moment im using the formula
=SUMPRODUCT(('w.e. 29th june'!$M$10:$M$105=LEFT(C$8,LEN(C$8)-2))*('w.e. 29th june'!$N$10:$N$105=RIGHT(C$8,1)),(OFFSET('w.e. 29th june'!$D$10,0,MIN(ROW()-9),96,1)))
(solution provided by a friend) and i simply copy and paste that formula to the cells in the summary table for the days for that week, from 23rd june to 29th june.
The problem i have is when i copy the formula down to the 30th of june, and change the 'w.e. 29th june' bits in the formula to 'w.e. 6th july', the formula fails to work anymore and I don't understand why, even if i adjust the w.e. 6th july tab so that its a mirror image of the w.e. 29th june tab (with the different figures).
I'm not sure if this is even the right approach either, maybe someone here knows a better way of doing the objective:
To be able to make a trend graph showing number of orders over time, split by Branch, and by Order type. The reason why i've laid it out the way I have is because when i create a pivot trend graph from it i can easily drop in which trend lines i'd like to see, for example, I can compare 'st albans E order type' with 'Peterborough E order type'. It also allows me to group the dates by month/weeks/years so i can change the amount of time displayed. So i would like to keep the layout in the 'data input' tab as it is, but maybe someone can come up with a better way of pulling the data from the w.e. tabs, either by modifying the way this SUMPRODUCT function i've been using works, or using a whole other function and approach altogether.
You will notice that in the w.e. 29th june tab i've had to tweak the data for it to work with the current formula (column B, M, and N and a VLOOKUP function) so i can group the various postcodes (AL , CM etc.etc.) by branch.
The whole approach i've taken is rubbish, i think, and would really appreciate some input on it. Hope i've made clear what i'm looking for! (i've highlighted in yellow when the formula stops working)
I'd eventually like to get to the point where i could press a button, and the raw data would be automatically imported into that summary table, and therefore the pivot table, so i could refresh the chart every week quickly.