+ Reply to Thread
Results 1 to 2 of 2

Simple invoice status monitoring dashboard based on pivot table

  1. #1
    Registered User
    Join Date
    08-31-2021
    Location
    WW
    MS-Off Ver
    Professional Plus 2013
    Posts
    6

    Question Simple invoice status monitoring dashboard based on pivot table

    Hello,
    I would like to create simple invoice status monitoring dashboard. My current idea is to create it based on the pivot table + pivot chart.
    The following description is a kind of simplification to make my needs easy to understand.
    Each month I will extract from my accounting system three kind of information:
    • invoice number
    • invoice issue date
    • current invoice value (this value changes depends on the payments received from the client)
    These information is represented as columns A,B,C in Sheet1 in attached file.
    Information in Column D is manually added by me and represents the month at the end of which there is the invoice value from the "Current invoice value" column.

    In Sheet2, I have created simple pivot table and pivot chart, where I would like to monitor overall invoices status (based on the pivot chart visualisation) and status per invoice (based on the pivot table).

    My two preliminary questions are:
    • How to change pivot chart construction so that on the X axis I will have month (without doing any changes in the pivot table).
    • How to modify the data series in pivot chart, so I will not have a columns splitted by invoices (just want to see overall value of the all invoices summarized, not per invoice).
    • Do you have any idea how to create an additional chart and pivot table that will show the status of all invoices (i.e. their current value), but without taking into account newly issued invoices? For example, I have data at the end of September which, apart from containing data on the value of invoices issued in previous months, also have the values of newly issued invoices, which distorts the information on the values of older invoices. Such a chart and pivot table would inform whether contractors are paying for older invoices.

    Thank you very much!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Simple invoice status monitoring dashboard based on pivot table

    To put the months in the horizontal axis go to: Pivot Table Tools > select data > switch column/row (I don't believe that you can put the months in the horizontal axis without changing the pivot table).
    To show solid columns not divided by invoice number move invoice number from the column area to the filters area and leave set at all, or remove from field list entirely.
    To omit the invoice amount that was issued in September put issued in the Filters area of the field list and deselect Sept-21
    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 to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. monitoring / tracking system with a front dashboard
    By cyberice in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-17-2020, 03:03 PM
  2. [SOLVED] VBA: Pull data based on time frame from Raw data or pivot table to dashboard sheet column
    By Sharonjit in forum Excel Programming / VBA / Macros
    Replies: 55
    Last Post: 12-20-2018, 10:00 AM
  3. Pivot Table Dashboard
    By vishnuuvm in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-03-2016, 02:11 PM
  4. Replies: 4
    Last Post: 11-04-2012, 12:43 PM
  5. Dashboard/pivot table
    By interested in forum Excel General
    Replies: 7
    Last Post: 02-08-2012, 03:31 PM
  6. MDB Pivot table to dashboard chart
    By GreenatExcel in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-08-2007, 04:27 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1