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

    Should I use Excel or Access or something else? (Office 2000 sr1a)

    I currently have an Excel spreadsheet that lists the date each laptop is borrowed and the date each is returned. Is there a way to find out (by looking at the previous year's records) how many laptops were checked out at one time? If I could find out (without having to manually go through and look) how many have been out at one time, that would answer my question.

    I have figured out that Excel can't do what I want it to do, at least not the way I have it set up. Is there some other program I could use in the future (or something that could help me now) that would help me?

    Thanks for any suggestions,
    Becky

  2. #2
    Silver Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    2,328
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Should I use Excel or Access or something else? (Office 2000 sr1a)

    Would Excel feature Data | Pivot Table and PivotChart Report Wizard can help you?
    Otherwise, the best program for it is Seagate CrystalReport.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Should I use Excel or Access or something else? (Office 2000 sr1a)

    Actually, you could use Excel to do this by creating a separate row for each day of the year, and a separate column for each laptop. Mark an 1 for each day that a laptop is checked out. The Sum of each row will be the number of laptops out on any given day. The Max of this column of sums sounds like the number you are looking for. If the number of laptops changed over the course of the year and you wanted a percentage, you could add a 0 for the days the laptop was not checked out; I think you could divide the sum by the count of nonblank cells, but I'm not an Excel person so I'm making this up as I go along. (The Sum of "Xs" for a column will be the number of days the laptop was out for the year. Maybe this would be useful for other purposes.)

    Now, the trick is to get an Excel VBA guru to write a procedure that will take what you have and generate the new sheet. I think it's highly do-able, but I am not an Excel VBA guru.

    Incidentally, while you could store this data in Access, extracting the data in a useful form probably would be more difficult. Hope this helps.

  4. #4
    Silver Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    2,328
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Should I use Excel or Access or something else? (Office 2000 sr1a)

    In addition to my post, Pivot Table Wizard is different in different versions of Office (Excel). Say, in Excel 2000 you can click Data | Pivot Table and PivotChart Report and in the wizard choose Pivot table. Then in the next step choose range - probably all your worksheet. Then choose the place to put pivot table - a new worksheet or better any empty cell outside data range you select in previous step. Now click Finish (Excel97 wizard has some more steps) and you will see your pivot table place. In "Drop Row Fields Here" drop the header of data/time column ('In", 'Out', 'Release' or whatever) from Pivot Table pop-up window. In "Drop Data Items Here" drop header of your laptops column ('Serial Number' I presume). Now you can see how many computers was registered each day/time. Caution: 'Serial Numbers" column must be formatted as Text, not as Number - otherwise you will see the sum of serial numbers!

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

    Re: Should I use Excel or Access(Office 2000 sr1a)

    Thanks for the replies...I think the pivot table is exactly what I need (unfortunately, my table isn't set up in a way that I can use it properly). Any suggestions on fixing that? I've attached the file-it's a way dummied down version of what we really use (my file is about 100 times to big to attach here!). My problem with the pivot table idea is that it doesn't include the dates between the pickup and drop off.

    P.S.-I've continued this post on the Excel board <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=91439&page=0&view =collapsed&sb=5&o=0&fpart=&vc=>here</A>.
    Attached Files Attached Files

Posting Permissions

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