Phase reporting

[Jonathan B.]

I am reposting since I understand a bit more what I am trying to do, but not
sure how to formulate it. We have a front page that displays everybodys
bi-weekly reports. We needed 2 more columns that display what percentage of
their project is complete and which of the 7 phases they are in.

The percent complete column is L101. L104, L107, L110, etc etc. merged
cells. So basically L101:L119.
I would like the formula to find the first <100% and report the title of the
phase in that same row A101:119. Even if there are other 100%s in the column,
it should report the first one it comes across since thats the earliest phase
that still needs to be completed. They shouldn't finish out of order, but
there is human error. (*0,-6)? There are merged cells between A and L.

If all phases are 100%, then report "Complete". It reads complete sometimes,
but I am not quite sure what it is looking for since it will report complete
when the first 6 are 100%. I would like all 7. I am thinking Complete should
equal 700%. It would probably be easier than looking for all rows to equal
100%.

And thank you Dave for the formula you gave me. I was just running into some
snags and I didnt quite know what I wanted to say the first time around.

Thanks ahead of time,

Jonathan

 

[jetted]

Hi

See attached workbook

Denis


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5094 |
+-------------------------------------------------------------------+

 

[Jonathan B.]

I would open it but due the the confidential nature of the business, our
company computers aren't allowed to download. Winzip has been disabled as
well.

Is there another route we could take?

Thanks,

Jonathan

 

[jetted]

Hi Jonathan

The spreadsheet looks like this(sample)

Name Phase1 Phase2 Phase3 Phase4 Phase 5 Phase6 Phase7 Completed
John 50%100% 100% 100% 100% 100% 100%
Denis100%100% 100% 100% 100% 100% 100%

the macro has the following code
Sub percentage()
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
Range("i2:I" & rowcount).ClearContents
For i = 2 To rowcount
Range("a" & i).Select
Selection.Offset(0, 1).Select
phase1 = ActiveCell.Value
If phase1 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 1"
GoTo line1:
End If
Selection.Offset(0, 1).Select
phase2 = ActiveCell.Value
If phase2 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 2"
GoTo line1
End If
Selection.Offset(0, 1).Select
phase3 = ActiveCell.Value
If phase3 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 3"
GoTo line1
End If
Selection.Offset(0, 1).Select
phase4 = ActiveCell.Value
If phase4 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 4"
GoTo line1
End If
Selection.Offset(0, 1).Select
phase5 = ActiveCell.Value
If phase5 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 5"
GoTo line1
End If
Selection.Offset(0, 1).Select
phase6 = ActiveCell.Value
If phase6 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 6"
GoTo line1
End If
Selection.Offset(0, 1).Select
phase7 = ActiveCell.Value
If phase7 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 7"
GoTo line1
End If
If phase1 = 1 And phase2 = 1 And phase3 = 1 And phase4 = 1 And phase5
1 And phase6 = 1 And phase7 = 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Completed"
End If
line1:
Next
End Su