Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Running Inventory
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Running Inventory
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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Running Inventory
Kazagi
I thought a bit more about your problem. I think that it is sit down with a pen and paper time. What do you want out of the system and how much time is there to input the data. These are the sheets I came up with Medicines Batch# Strength Date in Exp.Date Order# Qty Name === Supplier Manufacturer Address1 Address2 Town Country PostCode Tel# Fax# === Patients DOB Fname Lname Address Sex Next of Kin (NOK) PatientID ===== Dispensing Date PatientID DrugName Qty Strength ==== Then you would need your stock report something like my earlier reply. Do you want a Patient history? You could get one from the above tables. Of couse this would ideally be done on a database, but you can copy Excel Tables into Access sometiome in the future. Regards Peter A "Billy Liddel" wrote: 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Running Inventory
Peter,
First let me say thanks for your time in helping me with my problem, I truly appreciate it. I took your advice and wrote out what I needed to accomplish. Using some of your suggestions this would seem to do what I want. BTW I also have Access and don't really know how to use it any better than Excel - but I do have it if it will be easier. What I need is something that allows exact count of pills that are in the clinic, per type. This would change each time we dispensed samples, say like giving 30 Lipitor 10mg to Ms. Jones would change the total of 300 to 270. Simple, right? I don't know about that . . . ===== Medicines Name Lipitor Strength 10mg Batch# 12345 Exp.Date 02/2008 Qty 300 Class Statin Category Cholesterol-lowering ===== Patients Date 07/23/2007 Chart # 1098765 Fname Wilma Lname Flintstone DOB Long ago Next of Kin (NOK) Fred Medication Lipitor Batch# 12345 Exp. Date 02/2008 Qty Dispensed 30 ===== Now - other patient info will be found in chart. Use of database would allow easy recall/notification of patients should medicine be black boxed or pulled from market (Vioxx was yanked awhile back and had us pulling our hair out flipping back through paper records trying to determine who we had given any to). The 'in stock' count of medicines allows us to control/monitor and also may provide police/government agencies with needed info should there be need, i.e. theft or breakin. Time to build and implement is not a real concern as that we would be starting fresh without having to input old records. I just want it simple and effective. It seems so easy in my mind but I can't seem to get it to work. I need to tie the 30 I gave Wilma, probably by batch# and exp. date (because different drug companies may have same batch#'s but it would be highly unlikely they would also have same exp date) to the original 300, perform my calculation, arrive at new total and be ready to change that total the next time I give away some lipitor 10mg with same batch# and exp. date to then again arrive at yet another new and correct total number of Lipitor. Crystal clear, right? It should be easy, maybe I am trying to make an omelette when all I need is scrambled eggs? I do have Access ;-) Thanks again, Craig C. -- It is getting harder and harder to concentrate . . . what was I saying again? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Running Inventory
Craig
I'll have a look at this in Excel, it is an Excel Forum and it's years since I did anything on Access. My e-mail address is peter_athertonAThotmail.com. Do the obvious with the AT I'll send you a spreadsheet (when its done) for you to pass comments. Regards Peter "Kazdagi" wrote: Peter, First let me say thanks for your time in helping me with my problem, I truly appreciate it. I took your advice and wrote out what I needed to accomplish. Using some of your suggestions this would seem to do what I want. BTW I also have Access and don't really know how to use it any better than Excel - but I do have it if it will be easier. What I need is something that allows exact count of pills that are in the clinic, per type. This would change each time we dispensed samples, say like giving 30 Lipitor 10mg to Ms. Jones would change the total of 300 to 270. Simple, right? I don't know about that . . . ===== Medicines Name Lipitor Strength 10mg Batch# 12345 Exp.Date 02/2008 Qty 300 Class Statin Category Cholesterol-lowering ===== Patients Date 07/23/2007 Chart # 1098765 Fname Wilma Lname Flintstone DOB Long ago Next of Kin (NOK) Fred Medication Lipitor Batch# 12345 Exp. Date 02/2008 Qty Dispensed 30 ===== Now - other patient info will be found in chart. Use of database would allow easy recall/notification of patients should medicine be black boxed or pulled from market (Vioxx was yanked awhile back and had us pulling our hair out flipping back through paper records trying to determine who we had given any to). The 'in stock' count of medicines allows us to control/monitor and also may provide police/government agencies with needed info should there be need, i.e. theft or breakin. Time to build and implement is not a real concern as that we would be starting fresh without having to input old records. I just want it simple and effective. It seems so easy in my mind but I can't seem to get it to work. I need to tie the 30 I gave Wilma, probably by batch# and exp. date (because different drug companies may have same batch#'s but it would be highly unlikely they would also have same exp date) to the original 300, perform my calculation, arrive at new total and be ready to change that total the next time I give away some lipitor 10mg with same batch# and exp. date to then again arrive at yet another new and correct total number of Lipitor. Crystal clear, right? It should be easy, maybe I am trying to make an omelette when all I need is scrambled eggs? I do have Access ;-) Thanks again, Craig C. -- It is getting harder and harder to concentrate . . . what was I saying again? |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Running Inventory
ah, the days of reinforced punched hole ... I remember them well.
Have a summary sheet with the names of the medicines in A2:An, then in B2 =SUMIF(Sheet1!A:A,A2,Sheet1!E:E)-SUMIF(Sheet2!B:B,A2,Sheet2!F:F) where Sheet1 is the stock list sheet with the name in A and E the num, Sheet2 is the dispensed sheet with name in B and F the num. Then copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kazdagi" .(donotspam) wrote in message ... 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? |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Running Inventory
"Billy Liddel" wrote in message ... My e-mail address is peter_athertonAThotmail.com. Do the obvious with the AT I'll send you a spreadsheet (when its done) for you to pass comments. Billy Liddell - the old Liverpool winger? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table For Running Inventory Levels | Excel Discussion (Misc queries) | |||
help with my inventory | Excel Discussion (Misc queries) | |||
How can i get an inventory list that adds and subtracts inventory | Excel Discussion (Misc queries) | |||
bar inventory | Excel Worksheet Functions | |||
Inventory Pricing - running averages | Excel Discussion (Misc queries) |