Claudia has attended:
Excel Intermediate course
Cumulative variance
Hello,
I have a workbook with about 15 sheets with different stores. Each sheet holds information that I add to each week. i want it to automatically populate the variance from the last two weeks on information per sheet.
currently i have this manually where i just change the cell number but to do this for each sheet is time consuming.
RE: Cumulative variance
Hi Claudia,
Thank you for the forum question.
Yes what you want can be totally automated and you have a couple of options, but both options are advanced.
The data on your sheets must be stored in Tables. On the Excel Intermediate course you learned how to create tables (select a cell inside the data and press Ctrl T and click OK).
Append all the tables using Power Query. Query the data to always display the last two weeks, and create a PivotTable showing the result.
The video link below will show you have to append the tables.
https://www.youtube.com/watch?v=oeQPtbFNYIw
Next step is to filter the date to always display the last two weeks.
If you click on the filter in the query editor next to the date heading and click Date/Time Filters and click In the Previous, then you can define that you want only the last two weeks.
You will now have to add custom column to the table in the query. Add a column name and in the custom column box type:
=Number.ToText(Date.Year(["the name of your date column"]))&" "&Number.ToText(Date.WeekOfYear(["the name of your date column"],Day.Monday))
On the Home tab in the query editor click close and load. The query will now create a table in a new sheet in your workbook.
Next step is to create a PivotTable to display the variance.
Create a Pivot Table. Add your stores as Rows and your Values to values and the custom column to Columns. Right click on one of the values in the Pivot Table and click Show Values As and click on the option Difference from.
Base Field select the name of the custom column you created in the query. Select Previous in the Base Item list.
The Pivot Table will now always show the last two weeks variance. You just have to enter the data on the sheets.
Another option is to VBA code it, but you will need a very advanced knowledge of VBA to do this.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
