+ Reply to Thread
Results 1 to 12 of 12

Management of change help

  1. #1
    Registered User
    Join Date
    01-24-2013
    Location
    West Columbia, Tx
    MS-Off Ver
    Excel 2007
    Posts
    14

    Management of change help

    I get two excel reports that contain MOC unique numbers. One page lists the MOC number and if all acknowledgements are complete. The other page shows the actions that are associated with the MOC number. It could have multiple actions pertaining to the one MOC. These actions are open, completed or cancelled. I need to know when a particular MOC has all the action complete and/or cancelled. Then I would know that that MOC can be closed. If an action is still open then the MOC can't be closed. Does anyone have any suggestions?

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,159

    Re: Management of change help

    Sure, a few suggestions, but would be good to see a sample workbook. Counts the number of MOCs and then compare that count against MOCs complete and/or cancelled. If the count is the same, then most likely ready to mark as complete.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    01-24-2013
    Location
    West Columbia, Tx
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Management of change help

    I created a workbook that has Action Items and Days Open pages. The Days Open page lists the MOC number and has a column that tells you that Ack Completed or not. The Action Items page can have multiple actions associated to one MOC number, I deleted the company info. What I need is to find on the days open page, each one is unique number, MOC's with acknowledgers complete to go to the Action Item page to find the In Service status with no actions open.
    Attached Files Attached Files
    Last edited by panagle; 02-12-2019 at 03:56 PM.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,159

    Re: Management of change help

    If you post the mocked up workbook, it would help.

  5. #5
    Registered User
    Join Date
    01-24-2013
    Location
    West Columbia, Tx
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Management of change help

    the file was too large, just posted it.

  6. #6
    Registered User
    Join Date
    01-24-2013
    Location
    West Columbia, Tx
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Management of change help

    I deleted the other statuses on the actions tab.

    Cibolo is the name of the model of house that I had built. Small world
    Last edited by panagle; 02-12-2019 at 04:18 PM.

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,159

    Re: Management of change help

    Sorry, having a hard time deciphering what you are seeking.

    Can you walk me thru an example of what you would like to see? Or actually, update your workbook with that example.

  8. #8
    Registered User
    Join Date
    01-24-2013
    Location
    West Columbia, Tx
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Management of change help

    On the days open page, cell A44 = 2752017090005
    Then on the Action Items page, sort by MOC number and there should be 13 = 2752017090005 in the A column. If there are any of those 13 that has an open action then I can't close that MOC.

    One MOC =2752016120001, has 47 actions associated with it on the Action Items page. I have to have the Acknowledgers complete and all actions complete or cancelled for me to close the MOC.

    Is that any clearer?
    Last edited by panagle; 02-12-2019 at 04:37 PM.

  9. #9
    Registered User
    Join Date
    01-24-2013
    Location
    West Columbia, Tx
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Management of change help

    I need to know if there is a formula that can compare the 2 pages and let me know either all actions associated with them are closed/cancelled or there is an action that is still open.

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,159

    Re: Management of change help

    Okay, let's use row 44 in this example.

    In H44 >> =COUNTIF('Action Items'!A:A,'Days Open'!A44)
    In I44 >> =COUNTIFS('Action Items'!A:A,A44,'Action Items'!D:D,"Open")

    H44 equals 13
    I44 equals 2

    There are two of the 13 open

  11. #11
    Registered User
    Join Date
    01-24-2013
    Location
    West Columbia, Tx
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Management of change help

    That works, and thank you so much

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,159

    Re: Management of change help

    You are very welcome and thanks for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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