keep track of different expiration dates for the same item by autogenerating sheets- how to fix the issues i am having?
Posted on 2016-07-15
I am pretty new to Excel and what i want to do is way more challenging than my current skills can handle. Thats why i need your help fellow members.
I am volunteering at an african hospital and right now i am trying to create a virtual stock card with excel for every drug in the inventory.
The layout of the excelfile is finished so far but what is giving me a hard time right now are the expiration dates of drugs. I need to know when the different drugs are about to expire and keep an overview (yes I know about =date – today and formatting).
In fact, there would be no problem if there was only one expiration date for each drug but in reality its quite the opposite.
Lets suppose i ve got 3 deliveries of paracetamol for 5000 pills in total, 2000 pills expiring the 29.08.2016, 1500 expiring the 02.02.2017 and 1500 the 05.07.2017, i want to make sure to finish the first 2000 pills first to make sure that they dont expire.
So, to solve that problem, my plan is to keep one main inventory (with all the info) and to
1. create automatically for every expiration date one new sheet having the expiration date as a title and copying the concerned row in the mainlist to that autogenerated sheet (including he header of the maininventory)
2. any time additional pills are issued, indicate the expiration date of the concerned pills so that the concerned row gets copy pasted below the already copied rows in the appropriate sheet
3. the several autogenerated sheets should automatically calculate the total of pills sharing the same expiration date, the total of pills concerned issued and the total of concerned pills remaining so that you can quickly check how many of each are available.
Iv been working on that for some time now and i managed to get 1. to 3. to work but its all buggy and not complete yet. Please have a look at the excelfile while you keep readin because I find it difficult to explain.
so the problems i encountered are the following:
1. somehow the macro doesnt let me choose as a title a cell which is formated as a date (I get errors and no sheets are generated): to bypass that problem i added a different column which is generally formated and which refers to the cell formated as a date, although it looks ugly and doesnt make sense at the first glance it solved the problem. I tried hiding that column but when i do the cells in that column will not be copied to the autogenerated sheets. But I need to have these cells in dateformat on the autogenerated sheets so that I can calculate the expiration date. If you know any better method please let me know
2. i can run the macro one time, if i run it the sheets get autogenerated as wanted but it also generates one sheet containing all the rows which share one blank cell as a unique value (which is useless, looks ugly and just takes unnecessary ressources)- how to exlude the blank cells from the macro?
3.when I run the macro one time, the first sheet takes over the formulas for counting the pills (number received, issued, left,…) but the 2nd sheet does not do the same thing. The Last columns stay blank – why is that and how can it be fixed?
4. when I run the macro from a different sheet i get error messages and I don’t understand why case the macro should no be applied to active sheet but to the mainsheet no matter what sheet you have selected according to the code. Dont know why its not working.
5. when i run the macro again afer having added new rows to the mainsheet , not only the new rows are added but the ones which were already added before will be added again, so to clean up that mess I have to delete the duplicates everytime, is there a way to make sure that only new rows are taken into consideration or to automatically delete duplicates at the end of the macro (the end result being the same, having clean sheets)?So what would be needed is excel checking the presence of certain rows in the new sheets before pasting and, if they are not present, add the rows to the appropriate sheet and, else, doing nothing,
Your help would be greatly appreciated and may help conserving drugs and by that saving lives, so thanks a lot in advance!!:-)