Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-01-2014, 08:19 AM
ShankedS ShankedS is offline Auto-adjusting Formula (by date) Windows 7 64bit Auto-adjusting Formula (by date) Office 2010 64bit
Advanced Beginner
Auto-adjusting Formula (by date)
 
Join Date: Oct 2014
Posts: 62
ShankedS is on a distinguished road
Default Auto-adjusting Formula (by date)

I have a problem which may have a number of solutions. The solution I'm seeking after may not be possible, but another solution may present itself. If that's the case, then alternative solutions are certainly welcome.

The Problem
I work in a group home where we give medications on a daily basis. I'm responsible for making sure we don't run out of these medications. Every month I'm given an invoice to order more. However, not every medication fits into this cycle as neatly as it should, for whatever reason.

I also don't give medications on a daily basis, which means that I don't always get to see when they run low, and staff are notoriously bad at letting me know.

The solution, it would seem, would be to set myself an electronic reminder when a certain medication is running low. It wouldn't be terribly difficult to go through the meds as they are now and find out which ones are going to run out before the beginning of the next cycle.

The Solution
It comes in two parts. The first part is that when I do calculate the expected expiry dates, that I would have in place a notification, likely in Outlook, of when a medication is supposed to expire a week in advance of that expiration.

The second part includes Microsoft Excel. I have a LOT of reminders of things I need to do, and medications are important enough that I should have a redundant system in case I miss a reminder. As such, I'm trying to put together an excel file of the medications along with their expected expiration dates.

Doing it all manually would be easy. However, I'd love to make something that does a bit of the work for me. Specifically, once I put in the medication, how much of that medication is used on a daily basis, and how much is remaining, I'd like a formula that outputs the date I would need to order the medication. If that date comes before the next expected cycle, the medication would be highlighted and I would know to order it on the date given.

This may all sound relatively simple to someone who knows Excel well. However, Excel is a program I'm still figuring out.



How to use conditional cell highlighting is fairly simple to research through Google. However, when I did a search about countdowns, I got a lot of examples that use the Today() function. The problem with that function is that if I use it, then on any given day when I might look at the spreadsheet, the expiry date will be calculated given the medications as I saw them on a given day, but with the new date as the indicator. This will lead to a lot of problems if I'm relying on it.

The solution I would prefer is to have the "remaining" total of medication count down with each day in accordance with the value I provide in how much is used per day (which, as a side note, can be less than one. Some medications are given once a week, while other medications are given 3-4 times a week).

This is a bit more complicated and my basic Google searches have not dredged up how I might accomplish this.

Any and all help is appreciated, even on things which I can eventually find out with a bit of research (such as conditional highlighting) as I'm currently at work trying to do this order. However, as long as the main question is answered, I can find the other solutions myself.
Reply With Quote
  #2  
Old 12-02-2014, 12:19 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Auto-adjusting Formula (by date) Windows 7 64bit Auto-adjusting Formula (by date) Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Perhaps post a sample sheet showing what you have and the expected results
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 12-03-2014, 03:56 PM
ShankedS ShankedS is offline Auto-adjusting Formula (by date) Windows 7 64bit Auto-adjusting Formula (by date) Office 2010 64bit
Advanced Beginner
Auto-adjusting Formula (by date)
 
Join Date: Oct 2014
Posts: 62
ShankedS is on a distinguished road
Default

Working on it. Work is keeping me busy, but I'll get an example sheet up.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Date klaws Outlook 0 08-24-2012 01:01 PM
Auto-adjusting Formula (by date) adjusting remaining work per status date ketanco Project 1 03-16-2012 06:02 AM
Auto-adjusting Formula (by date) Formula to auto calculate Day of the week based on Date prasad@dmci.ca Excel 1 11-29-2011 01:05 PM
Auto-adjusting Formula (by date) Need a date formula MPAVLAS Excel 3 08-12-2010 10:04 PM
Auto-adjusting Formula (by date) Auto date alarm? markg2 Excel 3 11-18-2009 08:25 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:06 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft