F
fischer.johnf
I'm building a template that will sum store sales for variable dates depending on the user's needs. All my data is maintained in a pivot inside the workbook.
Currently I am able to do the above by copying and pasting values outside of the pivot table and then using SUM and OFFSET together to locate the store and sum across the date range. The formula looks something like this:
=SUM(OFFSET(A1,MATCH(STORE NAME,STORE NAME RANGE,),MATCH(BEGINNING DATE,DATE RANGE,),,):OFFSET(A1,MATCH(STORE NAME,STORE NAME RANGE,),MATCH(END DATE,DATE RANGE,),,))
A1 is my start reference on the pasted values from the pivot table and the Store Name and Beg/End Dates are linked to drop down menus the user can manipulate. The toggles give the user daily, weekly, monthly, quarterly, yearly options as well as calculates numerous metrics off the data.
While this method is effective, it requires me to copy and paste the pivot table every time i update and also doubles the size of my file since the data is duplicated. Any suggestions on how to get the same results right from the pivot table so I don't have to copy and paste?
Currently I am able to do the above by copying and pasting values outside of the pivot table and then using SUM and OFFSET together to locate the store and sum across the date range. The formula looks something like this:
=SUM(OFFSET(A1,MATCH(STORE NAME,STORE NAME RANGE,),MATCH(BEGINNING DATE,DATE RANGE,),,):OFFSET(A1,MATCH(STORE NAME,STORE NAME RANGE,),MATCH(END DATE,DATE RANGE,),,))
A1 is my start reference on the pasted values from the pivot table and the Store Name and Beg/End Dates are linked to drop down menus the user can manipulate. The toggles give the user daily, weekly, monthly, quarterly, yearly options as well as calculates numerous metrics off the data.
While this method is effective, it requires me to copy and paste the pivot table every time i update and also doubles the size of my file since the data is duplicated. Any suggestions on how to get the same results right from the pivot table so I don't have to copy and paste?