Results 1 to 4 of 4
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Setting up a Spreadsheet for Pivot Tables (2000 SR1)

    Before rearranging all of the data for a Pivot table (and I'd certainly keep Jefferson's advice in mind for subsequent spreadsheets), you might want to experiment with Auto Filters.
    Try the Data, Filter, AutoFilter menu. This will add the little autofilter drop down arrows. I'd pick the custom filter and filter by the date range you are interested in. Then I'd use a count function formula to count the number of entries that appear in the filtered list.
    If you haven't used filters I'd be happy to go into more detail - don't want to bore you if you've already used them.

    Cheers.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Spreadsheet for Pivot Tables (2000 SR1)

    I am not sure if I understand 100% what you want, but I suggest to name one cell where you put in the date of interest, then add a column next to your data with a formula that tells you if that date was in-between the dates in and out, e.g.

    =IF(AND(Date_of_Interest<C2,Date_of_Interest>B2)," YES","NO")

    and then use the COUNTIF function to count the number of times "YES" occurred in that column.

    e.g. =COUNTIF(E2:E18,"YES")

    Of course, you could make a pivottable with employee as data field and the heading of the new column as row field, then you would obtain a table with the counts of NO and YES.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting up a Spreadsheet for Pivot Tables (2000 SR1)

    I started this post on the General Office Solutions board, so you can go <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=off&Number=90996&page=0&vie w=collapsed&sb=5&o=0&fpart=>here</A>for a little history.

    Basically, I need to be able to take the attached file and make it so that I can create a pivot table off of it. I need to know the maximum number of laptops that were out at one time (this is a mini version of my real file). I figured out that I should have set up my spreadsheet differently, so I need some help fixing it so I can get the info I need. Jefferson suggested changing my format so that each calendar day has its own row, but I don't know how to make what I have now convert nicely. If anyone has any ideas, please let me know.

    Thanks,
    Becky
    Attached Files Attached Files

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thank you

    Thanks for the help. I was able to get the spreadsheet to work the way I needed it to, but I did have to set it up from scratch. Unfortunately, I wasn't able to transport the data over any easy way (at least it only had 2 years worth of data, and not much more). I did take Jefferson's advise to set up the spreadsheet, and I did figure out which Count formula to use (thanks to your advise, Catharine).

    I used COUNTA to count the number of entries per row, then I did a MAX formula to tell me the largest entries per row that I had. I was also able to use COUNTIF to figure out how long each attorney had borrowed a laptop (some of the numbers were pretty shocking!).

    Thanks to all for the help and suggestions. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Becky

Posting Permissions

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