I am working on a spreadsheet where each person has their own project and
each project has 7 different phases. I would like the spreadsheet to look
down the percentage complete column and find the first <100% and report the
project title in that row as the current phase even if they are working on
multiple phases. They may be 100% with the 4th phase but if they havent
finished the 2nd phase then that is the current phase to report to the
executives.

So I guess I am looking to have it count the column and the first non =100%
it comes across, report it and it alone. When they finish that phase, it will
go onto the next one.

When all of the phases read 100%, I would like for it to refer to a
different cell that reads, "Complete"

I hope this makes sense to somebody.

I am thinking I need a If True if False formula but I am not sure how to
formulate it.

=IF(ISERROR(OFFSET(A1,MIN(IF(A1:A14<1,ROW(A1:A14), ""))-1,0)),"complete",OFFSET(A1,MIN(IF(A1:A14<1,ROW(A1: A14),""))-1,0))


entered as an array formula shift ctrl enter

if the range of the % is a1:a14 ammend as appropriate
the red 0 needs to be ammended to the column with the title in 1=b 2=c
etc

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=563851

"Dav" wrote:


=IF(ISERROR(OFFSET(A1,MIN(IF(A1:A14<1,ROW(A1:A14), ""))-1,0)),"complete",OFFSET(A1,MIN(IF(A1:A14<1,ROW(A1: A14),""))-1,0))


entered as an array formula shift ctrl enter

if the range of the % is a1:a14 ammend as appropriate
the red 0 needs to be ammended to the column with the title in 1=b 2=c
etc

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=563851

Thanks Dave. I have used the formula and from some of the amendments I have done, it either reports the first row in the title column or "complete" if I put 100%s all the way down. Any idea how I could amend to make it skip the 100%s and report the next <100%?

I was thinking of having Complete =1400% For whatever reason it's saying
complete if I autofill 100% in the first 12 rows. I dont know why its
disregarding the last 2.