Forum Discussion

RobMacUK's avatar
RobMacUK
Copper Contributor
Feb 23, 2020

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 ...
excel
Formulas and Functions
ExcelExciting's avatar
ExcelExciting
MVP
Feb 23, 2020

Hi RobMacUK 

 

If you are making the PivotTable end of the day I would recommend you the following

  1. Maintain your data in one worksheet as MasterSheet
  2. Make sure your data is store in Excel Tables
  3. Make the Pivot Table
  4. 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

 

RobMacUK's avatar
RobMacUK
Copper Contributor
Feb 24, 2020

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 

  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Feb 24, 2020

    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

Resources