Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formulas (Office 2003)

    Hello Everyone,
    Attached is a spreadsheet that I created to track our issue of paper. I am not good at formulas, that is why I come to you guys.
    I need a break down by section and month issued, as you can see to the right. I will then use these numbers to create a chart.
    Thanks for everyones help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulas (Office 2003)

    The easiest way to do this is by creating a pivot table.
    - Remove the ON HAND quantities without a date, at the bottom of the data table (this is important!)
    - Click cell A1.
    - Select Data | Pivot Table and Pivot Chart Report...
    - Click Next >.
    - Excel should select the entire data table.
    - Click Next >.
    - Click Layout...
    - Drag SECTION to the Row area.
    - Drag DATE ISSUED to the Column area.
    - Drag ON HAND to the Data area.
    - Click OK.
    - Select the Existing Sheet option.
    - Select a cell, e.g. G3.
    - Click Finish.
    - Right-click the gray DATE ISSUED cell.
    - Select Outline and Details | Group... from the popup menu.
    - Select Months.
    - Click OK.
    Voila!

    You can also create a pivot chart from the pivot table. See attached version.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas (Office 2003)

    Hi Hans

    I have never touch Pivot Table before and I try the example you have provided......its is amazing!

    Will Pivot Table and the chart update itself if the dataset get update with new data?

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulas (Office 2003)

    A pivot table is not updated automatically. To update it, click anywhere in the pivot table and click the Refresh Data button on the Pivot Table toolbar (the exclamation mark). The pivot chart will automatically be updated when the pivot table is updated.

  5. #5
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas (Office 2003)

    Hans,
    Thanks, but it is not returning the information I am trying to get. I have attached a update worksheet with all my data entered.
    I am wanting a total quanity issued per section per month to show. I worked with the above formula and made some adjustments but with no luck. Instead of using ON HAND for Data area I used Qty Issue. When I do this it shows the number of times I issued an item to the section, not the quanity.
    Thanks for the help.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulas (Office 2003)

    You can double click the Qty Issue button and change the summary function to be used from Count to Sum. You can do this in the Wizard or afterwards, when the pivot table has already been created.

Posting Permissions

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