Kazdagi

I did not fully read your post before dashing off this simple stock.

Sheet2:

Date Medicine N In N Out Overall Total
02/01/2007 Aspirin 200 200
02/01/2007 Codeine 150 350
03/02/2007 Aspirin 25 325
04/02/2007 Aspirin 50 275
04/02/2007 Codeine 20 255
04/02/2007 Aspirin 50 275
04/02/2007 Codeine 20 255

Select a good range below and created range NAmes, Insert, Names, Create.
In B2 typed =SUM($C$2:C2)-SUM($D$2:D2) to give the overall Stock. Copy down
through the named range.

Sheet1 (Report)

In B3
Stock Levels
Aspirin 125
Codeine 130

In c4 type
=SUMPRODUCT(--(Medicine=B3)*(N_In))-SUMPRODUCT((--(Medicine=B3)*(N_Out)))

and copy down.

Can you work with this or would you like more detail. I'd also check with
your accountant that this method is OK before dispensing with the paperwork
and ledgers.

Regards
Peter A
"Kazdagi" wrote:

Ok, I thought this would be easy enough but the answer keep avoiding me.

I work in a small rural clinic and we get a lot of sample medications that
we provide to folks that cannot afford medicines. For about 3 years now I
have watched managment mismanage this inventory. Paper everywhere, notebooks
filled with reinforced holes in sheets that are folding over and driving me
crazy. There has got to be a better way of tracking this maddness . . .
Creating a spreadsheet to manage the meds is not hard, nor is one to track
meds dispensed to patients. What I am trying to do is create something that
has a accurate running total of meds in house. Say if I give 30 tablets of
drug yuckie to a patient I would want 30 subtracted from drug yuckie total.
Sounds easy but is totally perplexing to me. I have tried two pages - first
page with labels such as Name of Med., Strength, Lot #, Exp. Date, # of
tablets. Second page labels include date, Chart #, Name of Medicine,
Strength, Lot #, Exp Date and # of tablets dispensed (which I want to
subtract from the original number of that medicine and refresh the total to
reflect the new value. And I can't do it. Help and direction is what I need
- and perhaps someone that finds this very easy and interesting. I hate
seening thes binders and stacks of papers cluttering up workspace. . . there
must be an esier way! Thanks again,

Kazdagi
--
It is getting harder and harder to concentrate . . . what was I saying again?