+ Reply to Thread
Results 1 to 4 of 4

Phase reporting

  1. #1
    Jonathan B.
    Guest

    Phase reporting

    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

  2. #2
    Valued Forum Contributor
    Join Date
    12-16-2004
    Location
    Canada, Quebec
    Posts
    363
    Hi

    See attached workbook

    Denis
    Attached Files Attached Files

  3. #3
    Jonathan B.
    Guest

    Re: Phase reporting

    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" wrote:

    >
    > Hi
    >
    > See attached workbook
    >
    > Denis
    >
    >
    > +-------------------------------------------------------------------+
    > |Filename: Book1.zip |
    > |Download: http://www.excelforum.com/attachment.php?postid=5094 |
    > +-------------------------------------------------------------------+
    >
    > --
    > jetted
    > ------------------------------------------------------------------------
    > jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
    > View this thread: http://www.excelforum.com/showthread...hreadid=564693
    >
    >


  4. #4
    Valued Forum Contributor
    Join Date
    12-16-2004
    Location
    Canada, Quebec
    Posts
    363
    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 Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1