Evaluate a cell evalute 3 cell based on result

I have a small problem and I am new to excel. I have a number of students who come to our school. We trace the course of thier progress in a spreadsheet. When they leave we log the withdrawal date in cell b5. The cells B6, B7 and B8 are used to record their employment result on leaving, at 30 and 60 days. In cell B9 we display a result from the most recent recording as a positive or negative result.

I need a function that I can place in B9 that looks at B5 and if its empty does nothing. If it has a date in it, works from B8 - B6 looking for an entry. The first entry it find produces a positive or negative result. IE EMP, YOU or TOI in the cell would give a positive result, UNM or NOK would give a Negative result and NRI nothing.

I then need to apply this function down the page as many times as needed to evaluate other rows. Can anyone help me please?

Stevem

Put this in C9

=MAX(IF(B6:B8<>"",ROW(B6:B8)))

and in B9

=IF(C9=0,"",IF(ISNUMBER(MATCH(INDEX(B6:B8,C9-SUM(ROW(B6),-1)),{"IE","EMP","Y
OU","TOI"},0)),"pos",IF(ISNUMBER(MATCH(INDEX(B6:B8,C9-SUM(ROW(B6),-1)),{"UNM
","NUK"},0)),"neg","")))

both are array formulae, so commit with Ctrl-Shift-Enter, not just enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"stevem8435" <[email protected]> wrote
in message news:[email protected]...
>
> I have a small problem and I am new to excel. I have a number of
> students who come to our school. We trace the course of thier progress
> in a spreadsheet. When they leave we log the withdrawal date in cell
> b5. The cells B6, B7 and B8 are used to record their employment result
> on leaving, at 30 and 60 days. In cell B9 we display a result from the
> most recent recording as a positive or negative result.
>
> I need a function that I can place in B9 that looks at B5 and if its
> empty does nothing. If it has a date in it, works from B8 - B6 looking
> for an entry. The first entry it find produces a positive or negative
> result. IE EMP, YOU or TOI in the cell would give a positive result,
> UNM or NOK would give a Negative result and NRI nothing.
>
> I then need to apply this function down the page as many times as
> needed to evaluate other rows. Can anyone help me please?
>
> Stevem
>
>
> --
> stevem8435
> ------------------------------------------------------------------------
> stevem8435's Profile:
http://www.excelforum.com/member.php...o&userid=30887
> View this thread: http://www.excelforum.com/showthread...hreadid=505639
>