Row-based conditional search to give a cumulative result

**harsh2209** · 03-13-2010, 06:42 PM

I have is a test cases tracker for various test cycles. This is divided in two major parts:-
Master data fields - ‘ID’, 'Test Name’, ‘Created’, ‘Overall Status’, ‘Overall Result’
Cycle Fields – ‘Date’, ‘Status’, ‘Result’. The result field is ‘Pass’ or ‘Fail’. This field set repeats in each progressive cycles (cycle 1, cycle 2).

What I want to do:-
Populate an overall result of a script in the ‘Overall Result’ master field. The basis of this should be, if a test case has been executed in all cycles (let’s say 4 cycles), then what is the result when it was executed the last time.
Also, if a test was never executed, populate the Overall Status and Overall Result accordingly...

Attached is the example sheet.

Any thoughts, solution(s)…

**NBVC** · 03-13-2010, 09:21 PM

Try:

In D3:

=IF(COUNTA($F3:$Q3),LOOKUP(2,1/(($F$2:$Q$2="Status")*($F3:$Q3<>"")),$F3:$Q3),"Not Run")

in E3:

=IF(COUNTA($F3:$Q3),LOOKUP(2,1/(($F$2:$Q$2="Result")*($F3:$Q3<>"")),$F3:$Q3),"Not Run")

both copied down.

**martindwilson** · 03-13-2010, 09:30 PM

in d3
=iferror(INDEX($F3:$Q3,,MATCH("*",$F3:$Q3,-1)-1),"not run")
in e3
iferror(INDEX($F3:$Q3,,MATCH("*",$F3:$Q3,-1)),"not run")
dragged down should do it