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
Medium Priority
Last Modified: 2016-07-26

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!!:-)
Question by:Emmanuel S
LVL 85

Expert Comment

by:David Johnson, CD, MVP
ID: 41713586
Anything with an expiry date should be used in a FIFO order (first in/first out) only have available by lot number in your dispensary.

Your inventory should be managed so that new items are only ordered on an as required basis..  i.e. for item x your average usage is Y units per day, and it takes Z days on average to get restocked. So what you have is a quantity on hand, reorder quantity, order quantity.
Reorder Quantity = average useage * reorder delay (days) so when your quantity on hand reaches the reorder quantity you place the order.

If your average usage / (today - expiry date) quantity i.e. you have more of an item that will expire before the expected usage date then you might want to reach out to other hospitals and arrange a trade. so you will both get something you need and not have to dispose of medication. new Boxes of medication should be placed at the bottom of the pile.

Expert Comment

ID: 41713616
Include the spreadsheet you referenced.

Author Comment

by:Emmanuel S
ID: 41713845
Thank you for your feedback. I know of the FIFO principle thats exactly why i want to keep track of expiry date. One of the problems is that the drugs we are getting are not only drugs we ordered but a lot of donations as well. Trading with other hospitals sounds like a good idea and may be possible at some point in future but as you can imagine all the hospitals here lack in the right tools and especially when it comes to data analytics.

 It would probably help to take a look a my excel sheet to get an idea about what i ve already done. Actually i thought that it was attached to my initial question but now it turned out that it isnt, sorry my bad. Didnt realise that i had to do an additional step after choosing the file by pressing the upload button. I think providing you the spreadsheets and the code will help you to get a clear picture.

This is the first time that i am using this website. I know that it general people ask one question and the the community works towards an answer but i am facing many technical issues. Would you suggest me to introduce a separate question for every single technical issue i a having or leave it the way it is now?
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 24

Accepted Solution

Ejgil Hedegaard earned 2000 total points
ID: 41716076
Try attached workbook.
Expire dates in column D changed to real dates.
If sheet for expire date exist, it is cleared and reused.
Press button on sheet STOCK CARD to run.
The macro is in module 2.

Author Comment

by:Emmanuel S
ID: 41720917
Thank a lot Ejgil I like your approach!With your macro no more spreadsheet is created for the blanks and using a button to run the macro is a good idea as well.
While the macro does exactly what I need it to do at the first glance when pressing the button one time it seems as if it s running out of control once I change data inside the stockcard sheet and run it a 2nd time:

when I enter a new date in column d66 for instance and run the macro, a spreadsheet having that name is indeed created but the appropriate line is not copied to that newly generated sheet, the sheet being left all blank right now.
when I enter the same date in column d66 that line is not getting copied into the already existing date sheet
in both cases I get as error message runtimeerror 2004, autofilter method of range class failed =(
LVL 24

Expert Comment

by:Ejgil Hedegaard
ID: 41720954
The sheet is made because the "date" is different from the others.
But if it is entered as a text looking looking a date, the autofilter fails, because a date is expected.
Enter dates using / like this 1/5/16.
You don't have to type all, Excel translates, and due to cell format it is displayed as 01.05.16.
Select the cell, and look in the formula line above the sheet.
If it is 01.05.16 it is a text.
A date looks like 01-05-2016, also when display in the cell is different.

Author Comment

by:Emmanuel S
ID: 41724901
amazing its working Ejgil, thanks a LOT!But I am afraid to admit I but it was for nothing as I realized on the way that my approach was not the right one, so I ended up wasting many hours on something which I can throw in the trashbin now :-( I hope you didn't invest too much time in the coding. I d still like to keep your file as a sample though can be useful some day.

You seem to be quite skilled in excel. I need to start over and I d like to know your opinion on my new approach. I didn't do any coding yet but the excel clearly shows what I want to achieve and how i want to achieve it. I d have saved many hours if i had done that from the start :-).
Can I send you the excel file in private? It would be great if you could give a hand on the new version as well, this time hopefully your support will not be in vain!Thank you very much!!!! :-)
LVL 24

Expert Comment

by:Ejgil Hedegaard
ID: 41725103
The idea of EE is that questions are public, for search reasons, and to have more experts tune in with answers.
Close this question, assigning point to the answer.
Then make a new question and attach the workbook.
It can be dummy data.
The important issue is that the layout is the same.

You can make a link here, then I get a notice.

Author Comment

by:Emmanuel S
ID: 41729517
Ok so I managed to solve the expire date challenge by myself by using functions, no macro needed yeppieee!:D
But unfortunately I now need a macro for something else:


The idea is that I combine prefiltered data from 2 sheets so that I can easily identify human errors.
Please have a look:


Thank you :-)

Author Closing Comment

by:Emmanuel S
ID: 41729519
thank you very much!

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question