+ Reply to Thread
Results 1 to 7 of 7

Pivot Table help - adding totals and sorting by date -

  1. #1
    Registered User
    Join Date
    07-29-2009
    Location
    San Diego
    MS-Off Ver
    Excel 2007
    Posts
    4

    Pivot Table help - adding totals and sorting by date -

    I've attached a file with some sample data and some text explaining what I'm trying to do.

    I work at a parts factory, and log every part I inspect into a spreadsheet. In one of the columns I record the date, and in another I record the quantity of parts. (Usually it's just 1, but sometimes it's multiple.)

    I could easily get the total parts so far this year, but I would like to break it down per month.

    I know I could go back and manually count how many I did per month, but I would like it to work automatically as the year progresses.

    Does this make sense to anyone? If you'll look at the very top of the "2009" sheet in my example, you'll see some text I added to try and further explain what I'm talking about.

    (I know many of you could probably do this in your sleep, but I don't know where to start...)

    Thanks in advance if anyone can help!
    Attached Files Attached Files
    Last edited by DBM; 07-29-2009 at 03:50 PM.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: This may be basic for you guys, but I'm lost on how to do this...

    In Excel there is almost always more than one way to get the job done.

    I think a Pivot Table (PT) is the ticket, especially when they are based on a dynamic named range for the data set.

    Once the PT is created you can set it to Group by month.

    See the attached for an example PT. I created a dynamic named range for the data, named it "MyData" and used this as the source for the PT, then set the Group option to Month.

    All you need to do, as data is added/deleted, is right click the PT and choose Refresh (this could automated with VBA).

    As for the side question in your file. Please post it in a new question on the forum. I will give you a hint: create a look up table that contains the part numbers referenced to their nomenclature and use VLOOKUP to return a match.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-29-2009
    Location
    San Diego
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: This may be basic for you guys, but I'm lost on how to do this...

    You are a true American hero!!!

    One thing though: The table seems to be simply adding how many cells have numbers in them, but not the actual numbers.

    For example if I go back over to the "2009" sheet and change an entry in the "quantity" column from 1 to say, 1000, the total is not increased. Shouldn't it be increased by 1000 for that month?

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: This may be basic for you guys, but I'm lost on how to do this...

    You have to refresh a Pivot Table when the values in the data source change.
    Did you right-click the PT and choose Refresh after making the data changes?

    My earlier comment:
    All you need to do, as data is added/deleted, is right click the PT and choose Refresh (this could automated with VBA).

  5. #5
    Registered User
    Join Date
    07-29-2009
    Location
    San Diego
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: This may be basic for you guys, but I'm lost on how to do this...

    Yes. I drew an elaborate picture in paint to help illustrate what I'm talking about..
    Attached Images Attached Images

  6. #6
    Registered User
    Join Date
    07-29-2009
    Location
    San Diego
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: This may be basic for you guys, but I'm lost on how to do this...

    Never mind! I figured it out finally...

    Thanks a million again!

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: This may be basic for you guys, but I'm lost on how to do this...

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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