14 Aug 09: �Glenn, I couldn't tell if my reply post saying thank you for your
help was received. �Your correction, of my stab at the IF statement is
working perfectly. �Now that we are using the spreadsheet. �I have been faced
with the question: How do we stop the formula from processing if the patient
has either had an appointment or surgery? � The simple answer in my mind lies
in Columns F & G or �J & K. �Column F & G asks does patient have an
appointment and we just place an x in the Yes or No column. �The same is true
in J & K except there we ask is the patient having surgery Yes or No. �It
would seem if we put a "X" in one of those columns then we could stop the
clock so to speak. � So I'm thinking, I need a STOP statement stating if
column J or K has a text entry "X" then stop processing formula in columnE,
Alert Status.
=IF(OR(AND(C4="E",D4>=7),AND(C4="U",D4>=30)),"ALERT",IF(AND(C4="R",D4>=120)�,"Review",""),AND(J4="X",STOP E4),OR(K4="X", STOP E4))
Any guidance is greatly appreciated.
One of the great viruses which infects programmers (and you are
programming your spreadsheet) is the WT*DIDT (What The *** Did I Do
There) virus, and its more deadly variant (Why the *** Did I Do That).
If you go on adding conditions you will certainly be infected, and it
has a 6 month incubation time. <G>
I would suggest you use a small array (in a hidden part of your
spreadsheet) as a lookup table. Because your limit days are not
contigous you need to use a slightly different format than the usual
VLOOKUP(Index, Array, constant_offset) function. You should use MATCH
() to find the offset
Lets start with the table, I show my rows & columns so that you can
understand the formula. You would need to change them to suit your
spreadsheet. Note that all look-up lists must be in ascending
order. . . .
Column N O P Q R S T
Row
4 -1 8 31 121 1500
5
6 E ALERT ALERT ALERT ALERT
7 R REVIEW REVIEW
8 U ALERT ALERT ALERT
Then use the formula for your warning text in row 5 as ...
=IF(ISBLANK(C5),"",IF(COUNTIF(J5:K5,"X")=0,VLOOKUP(C5,$O$5:$T$8,MATCH
(D5,$P$4:$T$4,1)+1,TRUE),""))
If C5 is blank soak up the error text.
Else if C5 is good, Count any X in cols J & K, if there's none then
lookup in column O the "E", "R", or "U", and offset into the table by
the match into row 4. Note that "empty" cells in the look-up table
must have a space character in them, and the MATCH() row must have one
more than the limit because if match cannot find a match it takes the
column before it (the final 1 match-type parameter). The result of
MATCH() also needs the +1 because the VLOOKUP index starts from 1 as
the column index. Note the -1 (less than 0) & 1500 (or some big
number) to ensure that there is always a number to match.
Note the use of "$" to make absolute references (inserted by F4
presses with the cursor in the reference).
If you do the above you will always understand what you have done
(even after six months), and can readily alter your table to add
additional alerts as the users ask for them (...and they _will_<g>).
Alan Lloyd
