Forum Discussion
Referencing the data source for a pivot table
Hi,
I'm developing our annual expenses workbook to make it easier to copy and set up for a new year.
Can anyone help me as to how I can reference the data area for a series of pivot table to be local to the worksheet or get it to lookup the name of the worksheet it is in? This will prevent me from needing to adjust 96 references across the 12 monthly worksheets. I may not have explained this well so here's a picture to show the reference I'd like to change.
So is there a function to change the "Jan" ref to be the generic worksheet or a way of referencing a text box in which I would have "Jan" or another month?
Thanks for helping me out with this. Kind regards Rob
3 Replies
Hi RobMacUK
If you are making the PivotTable end of the day I would recommend you the following
- Maintain your data in one worksheet as MasterSheet
- Make sure your data is store in Excel Tables
- Make the Pivot Table
- Add Date Slicer In your report that will filter the information blink of an eye 👀.
Note : Important thing here is the date, correct date will give correct info.
Hope this might be helpful for you.
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com
ExcelExciting Thank you for your reply Faraz and also for replying so quickly. That's an interesting idea, but can a slicer be used to determine the data for multiple pivot tables. There are 9 pivot tables on each monthly sheet already to cut the month's data up for accounting entries and to keep running totals on the different types of mileage expenses.
Kind regards
Rob
Hi RobMacUK
Yes you can connect one slicer to multiple Pivot Tables. Add the slicer > right click > report connections. Select Pivot Need to be connected to the slicer
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com
