+ Reply to Thread
Results 1 to 12 of 12

Consolidating Filters in Pivot Tables

  1. 02-06-2017, 02:18 PM #1
    nmbell
    nmbell is offline
    Registered User
    Join Date
    07-08-2016
    Location
    Pensacola, FL
    MS-Off Ver
    2016
    Posts
    20

    Consolidating Filters in Pivot Tables

    I am trying to figure out how to use a pivot table to be able to show much much individual vendors have invoiced per month. On the first tab of the attached sheet I have a summary of the information. The second tab lists the individual invoices. I am trying to insert pivot tables following these tabs that would allow me to cleanly shows how much each vendor has invoiced during a certain period that aligns with our owner billing period. I believe I have done that correctly using date filters.

    I am having a few problems streamlining the information though. I can not figure out why there is a SUM of the amounts I need AND a total sum. What I want is a sum of the Taxable Amount and then a sum of the nontaxable amount so that I can calculate the current period sales & use taxes as done in the Nov. & Dec tab.

    After that I am trying to figure out the best way to continue tracking for the following months. Do I just copy the tab and then reset the filters?

    Any help is greatly appreciated! Thank you!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-06-2017, 05:13 PM #2
    dflak
    dflak is offline
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,946

    Re: Consolidating Filters in Pivot Tables

    See if this will work for you.

    First of all, you do not have to add blank rows at the end of a table. When you add new data to a row directly under the table, the table will expand to include the row.

    Secondly why compute the tax "out of table". Compute it as a helper column in the source data.

    To get rid of the extra totals at the end of the column Right Click on one of them. One of the options that comes up in the menu is to remove them.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
    Reply With QuoteRegister To Reply
  3. 02-07-2017, 02:11 PM #3
    nmbell
    nmbell is offline
    Registered User
    Join Date
    07-08-2016
    Location
    Pensacola, FL
    MS-Off Ver
    2016
    Posts
    20

    Re: Consolidating Filters in Pivot Tables

    Thank you. I have been able to remove the grand totals. However, I need to be able to break down the sums by month so I can determine the taxable amount for the period then calculate the correct sales and use tax based on whether the invoiced amount has exceeded the $5,000 threshold.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 02-08-2017, 12:39 PM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,708

    Re: Consolidating Filters in Pivot Tables

    If I understand correctly you are looking to add a couple of 'Calculated Fields' to your pivot table. The first calculated field gives the 'Tax this Period' based on the formula that populates B18. The second calculated field gives the 'Total Invoice this Period' based on the formula that populates C18.
    In order to upload a copy of the workbook with the calculated fields applied I either need the password to unprotect the NON TAX sheet, so that I can remove my personal information, or for you to upload another copy of the workbook that doesn't include the NON TAX sheet, assuming the PT will work without it.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  5. 02-08-2017, 03:38 PM #5
    nmbell
    nmbell is offline
    Registered User
    Join Date
    07-08-2016
    Location
    Pensacola, FL
    MS-Off Ver
    2016
    Posts
    20

    Re: Consolidating Filters in Pivot Tables

    THat is correct. I have tried to figure out how to add them but each time I get some error!

    I am sorry! I didn't realize the passwords were still on. It should be 1201.
    Reply With QuoteRegister To Reply
  6. 02-08-2017, 03:50 PM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,708

    Re: Consolidating Filters in Pivot Tables

    I get a popup saying that password is not correct.
    Reply With QuoteRegister To Reply
  7. 02-08-2017, 04:17 PM #7
    nmbell
    nmbell is offline
    Registered User
    Join Date
    07-08-2016
    Location
    Pensacola, FL
    MS-Off Ver
    2016
    Posts
    20

    Re: Consolidating Filters in Pivot Tables

    My assistant says its 5053 then
    Reply With QuoteRegister To Reply
  8. 02-08-2017, 04:22 PM #8
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,708

    Re: Consolidating Filters in Pivot Tables

    Still not working. I suggest that y'all unprotect or take the protected sheet out (if not necessary to pivot table) on your end, and upload again.
    Reply With QuoteRegister To Reply
  9. 02-08-2017, 04:24 PM #9
    nmbell
    nmbell is offline
    Registered User
    Join Date
    07-08-2016
    Location
    Pensacola, FL
    MS-Off Ver
    2016
    Posts
    20

    Re: Consolidating Filters in Pivot Tables

    This one should have the non tax item removed. I apologize. I didn't even know it was there!!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 02-08-2017, 04:54 PM #10
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,708

    Re: Consolidating Filters in Pivot Tables

    Take a look and see if this is what you want.
    The formula for the 'Tax This Period' field is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    The formula for the 'Total Invoiced This Period' field is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 02-09-2017, 10:43 AM #11
    nmbell
    nmbell is offline
    Registered User
    Join Date
    07-08-2016
    Location
    Pensacola, FL
    MS-Off Ver
    2016
    Posts
    20

    Re: Consolidating Filters in Pivot Tables

    This is great. One more question. Do you know how I would add a field to calculate the total of previously invoiced taxable amounts? I need to be able to verify that the sum of all previous months invoices is >= 4999.99
    Reply With QuoteRegister To Reply
  12. 02-09-2017, 11:56 AM #12
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,708

    Re: Consolidating Filters in Pivot Tables

    See if Sheet 1 is what you want. There are actually two pivot tables on the sheet, with a slicer tied to both. The row labels for the second pivot table are in column C and have been hidden.
    Let us know if you have any questions.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Consolidating Pivot Tables into 1 pivot table - is this possible?
    By levitt in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-20-2013, 04:10 PM
  2. Linking filters for two pivot tables
    By mo0ki in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-13-2012, 09:38 PM
  3. Excel 2007 : Pivot tables filters
    By Squevil in forum Excel General
    Replies: 1
    Last Post: 11-04-2011, 10:30 AM
  4. Pivot tables - Multiple filters across 2 tables
    By shoesterix in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2011, 05:02 AM
  5. Pivot tables with dynamic filters
    By Scotty81 in forum Excel General
    Replies: 0
    Last Post: 05-08-2009, 11:04 AM
  6. [SOLVED] Consolidating multiple external pivot tables into one pivot table
    By Exiled in forum Excel General
    Replies: 1
    Last Post: 08-13-2006, 07:51 AM
  7. [SOLVED] Advanced filters in Pivot tables ?
    By [email protected] in forum Excel General
    Replies: 1
    Last Post: 12-14-2005, 11:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1