Enhancing a RASCI Model to avoid different input to same assigned Priority

Posted on 2011-10-02
Last Modified: 2012-05-12
EE Professionals,

I have a very nice workbook that several EE Professionals wrote for use;  I have two problems with it.  One is a design problem that I didn't anticipate and was my fault by not indicating it in the specifications.  The second issue is a design issue for output, which I will post in a new related question once the first issue is resolved.  I have attached the actual code in the file and I believe the modification I am looking at is in Sheet 1 of the VBA Window.

Overview of Problem:

The RASCI Model is a Project Management tool that simply allows you to identify a set of tasks, put them in priority sequence and display who has responsibility for what.  The model attached works fine EXCEPT when you add an additional RESPONSIBILITY to the same task (by adding it to the first blank cell in column A) you can get erroneous results if the Text doesn't exactly match or the user inadvertently adds different text to the same priority number.  So for example, if I were to put in "1.1" in cell A (first open cell in column), and 1.1 was already a Task, the addition of another assignment to the same task, 1.1, should display THE SAME TASK.  This is so you can assign another responsible Functional Area to the same Task.  It allows this now so when you put in 1.1, it will combine the results on the Output beginning in Column H.

What I'm trying to accomplish:

In order to avoid mistakes and bad results, I'd like for the macro to check to see if the assigned number exists, and if so, is auto populate with the previous description IF and only if, a duplicate number appears. This will prevent the user from putting in a different task for the same assigned priority.  So, if I put in 1.1, and 1.1 already exists, it will duplicate the text description in Cell B.  That's it!
Question by:Bright01
    LVL 19

    Accepted Solution

    You could use this :

        '-- auto populate existing processes on new entry
        If Target.Column = 1 Then
            If Target.Row > glOldRows Then
                Set result = Range("A4:A" & Target.Row - 1).Find(Target, lookat:=xlWhole)
                    If Not result Is Nothing Then
                        Application.EnableEvents = False
                        '-- fill in process name
                        Target.Offset(0, 1) = result.Offset(0, 1)
                        Application.EnableEvents = True
                    End If
            End If
        End If

    Open in new window

    as in  Copy-of-RASCI-Sheetv76-Sample-r6.xlsm

    be sure though to thoroughly test all regular use scenario's (does it work as you expect when updating rows, deleting rows etc)

    Author Closing Comment


    I tested the workbook and it works very well.   I believe that any of the slight issues are probably a function of having the table on the same set of rows as the input.  I think if I have the output placed on a different tab/sheet, I think it will ease some of the complexity that surrounds this set of Macros.  I'll be asking a related question to try to move the output to another tab.

    I appreciate your insight and help with this.

    LVL 19

    Expert Comment

    No problem !
    glad to have been of service...

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now