iPhone X review
I have an elderly neighbour who takes several tablets per day, but she tends to forget when to re-order a new prescription.
I have tried using Excel to do a calculation but I am having a problem when I update the spread sheet.
I have started by making cell D3 (Medication Stock) which is the current stock level at the start.
In Column C I have the date running down. In D4 I have the simple formula (=D3-2) 2 being the number of that type of tablet per day.
I have then conditional formatted the cells so that (a) when the stock level goes below 10 it highlight orange. And (b) when at zero highlights red.
However, when I try to edit the sheet by deleting the dates that have past, it comes up with an error, #REF!
Can anyone help with my problem, or suggest a better method.
I have my prescription managed by the Pharmacy.
If your neighbour has a regular prescription the Pharmacy will order the repeat a few days before its required so that its ready to collect. My Pharmacy gives me a little note with the date next due which I stick on the fridge door. They will always order earlier if required and I just give them a phone call. Most Pharmacies operate this service and if your neighbour isn't able to visit many will also deliver.
Same as BT and Chemist delivers them. The Chemist keeps the records.
Vog, thanks for your reply, but I think I will end up with a sheet that will be very long, 365 rows per year !
Also, the lady is a “silver surfer” and likes to keep her mind going, Excel formulas are a little difficult for her (AND ME !!!) , and getting the chemist to do the work is too easy.
So after a day looking at the exercise, here’s my solution.
I have renamed sheet 3 as “data” and used it just to “dump” facts and figures.
On the data sheet I have a calculation for the number of days between the “stock check” and “today”, and also the “today()” formula
In Cell B5 I put the date whenever the stock number of tablets is known.
In C4 is the stock number of tablets at a set day.
In C7 (Balance cell) I have the formula “=C4-data!D8*2” . The two at the end is how many tablets per day.
In C8 I have the formula “Today()+C4” (C4 being the stock number at a particular day.) this shows the date when the tablets will run out.
I have conditionally formatted the “Balance” cell so that it turns red when the total left is below 10.
Just one final question, Is there a way to Conditionally Format the “Run Out date” cell (C8) to change colour, say I week before that date arrives?
Any comments would be appreciated.
For C8 try the CF formula
This thread is now locked and can not be replied to.