Summarize data from multiple sheets (2003 SP 2)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote