Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summarize data from multiple sheets (2003 SP 2)

    I've been asked to develop an Excel workbook to track a certain medication outside our normal methods. We have to send a weekly report to the hospital director, so it'll be a running totals sort of thing. I have seven sites in different towns where it can be sent to and/or dispensed from. I have users of widely varying computer skills potentially entering data. Ideally, I guess there would be a table of contents page to go to the right site, then enter the amount received or dispensed. There would be a summary of all sites either on the table of contents sheet or the second sheet. I have seen demos of something like this, but it's been so far back I don't recall how it's done. Searched Microsoft.com and the Lounge, didn't find anything. I have attached a file I was given and asked to fix; hopefully that will help. Thanks a million for your help, as always.
    Attached Files Attached Files
    Jim Whitt
    Pharmacist
    Temple, Texas

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

    Re: Summarize data from multiple sheets (2003 SP 2)

    I'm not sure Excel is the best application for this. If data are to be entered by several people on the same local area network, I'd use an Access database. If data are to be entered by people in different locations, I'd tend to use a web application.

    The workbook you attached doesn't give us much to go on, since all sheets except the summary one are empty.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarize data from multiple sheets (2003 SP 2)

    And, the summary sheet doesn't give a clue as to what is being summarized or how.
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarize data from multiple sheets (2003 SP 2

    You're both right, I didn't give you enough to work with. I'm sorry. I'm still trying to figure out what the supervisors really want this to do. I thought they wanted a running total for ALL the sites, which gives false totals if you try to use a Pivot Table. I agree that the whole thing would be better off in Access, and I'd just do a simple application. I have to sell that idea - it's amazing how one little word (Access) can scare so many smart people at once!

    What I have tried to do now with this workbook is offer two theories of the crime - er, job. One uses a Pivot Table, the other uses links from the individual sites' worksheets. The numbers are made up, so just ignore the numbers not matching with the two different methods. Also, ignore the last sheet - that's what I was originally sent. I'll continue to work toward getting a clearer idea of what they want. Thanks.
    Attached Files Attached Files
    Jim Whitt
    Pharmacist
    Temple, Texas

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

    Re: Summarize data from multiple sheets (2003 SP 2

    If at all possible, you should do away with the different sheets for the sites, and use a single sheet on which all data are entered, with an extra column to identify the site. That would make things a lot easier.

  6. #6
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarize data from multiple sheets (2003 SP 2

    Thanks for the tip. My IT service and I figured a way to do it on our "mainframe" system by creating a database that uses lookups to other files to speed up data entry and ensure consistency. I thank you again for your time. So long for now.
    Jim Whitt
    Pharmacist
    Temple, Texas

Posting Permissions

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