Frank

I've set up a worksheet (one for each doctor) with a heading in A1 of
'Active Scripts'. In A2 a heading of 'Future Date'

Starting in row 4 I have the following headers

A4 Patient
B4 StartDate
C4 No Days
D4 EndDate
E4 Active?

You then put a patient ID in the next row (Cell A5) a start date for the
course in B5 the number of days in C5. In D5 the formula

=IF(B5="","",B5+C5)

then in E5 the formula

=IF(OR(A5="",B5=""),"",IF($B$2<"",IF(D5<$B$2,"No" ,"Yes"),IF(D5<TODAY(),"No","Yes")))

Then in B1 enter the formula

=COUNTIF(E:E,"Yes")

You will then have the count of open scripts at today's date in B1 UNLESS
you add a date in B2 and then this will be the date that controls how many
open scripts there are

See if that works for you. Obviously you can copy the formulas in column D
and E as far as you like. They will show nothing until a patient ID is
entered, etc.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"Doctor Frank" wrote in message
...
Hello, I wonder if you might be able to guide me in the right
direction. I am a physician and need a bit of help. One of the
medications I prescribe is limited to prescribing it to only 100
patients at a time. This number is based on the script activity. For
example if I write for 30 days worth of the medication on January
first,,, that counts as 'one patient' for the next 30 days. If I
write for another patient on January 1st for 15 days worth of the
medication,, then he counts as a hit for the next fifteen days. So,
from the 1st to the 15th,, I will have '2' patients,, then after the
15th, I drop to one patient as the one patients script has expired.
Want to have a simple interface where the Doctor taps his name,,
enters a medical identifier for the patient and taps how long the
script is active for. Then the number of active patients appears in a
box. This is so the doctor does not have any more than 100 patients
active at any one time. One should also be able to type in a date in
the future,, say January 19th in this example,, and get the box to
show that on that date you will only have '1' active patient. Any
guidance would be appreciated,, I am doing this to help a patient
group that is in need,, I have no financial interest in selling this,,
just want to do it to help out. Thanks,,



F. Kunkel, MD