Moving the Output to another Worksheet

Hi Bright,  please advise how the output is generated.  What code/macro or process do you use in your workbook to accomplish that?  I took a look at that in prior questions, and I noticed the formulas which lookup to determine how to layout the "project plan".  Is this all formula based, or can you point to the code or your process which is in addition to these formulas?

If its all formula based, it should be modified quickly, without need to be concerned with process.  However, if there are code/process steps, we need to know what/where they are to ensure that things run smoothly.

Dave

Dave,

Welcome back!  This is a stand alone workbook so the data does not come from another source.  It is put in manually as a project is built out.  I would like to be able to cut and paste a set of tasks with priorities into the input section but other then that, it should stand alone.  The output is generated from Columns A through E over to H through AF.

Does that make sense?

Thanks in advance,

B.

Questions:

1.  What is column BA used for?
2.  When I look at A:E, I don't see the same thing in H:AF.  It appears to me that column H is a list of tasks, and J:AF are formulas that lookup from columns A:E.  So do you manually copy something from column B over to column H and the rest just happens by formulas?
3.  What CURRENT step are you taking that invalidates the output in H:AF as it stands today?

Thanks,

Dave

Dave,

1. Column BA is a list table....that is all it's used for.  It shows up in Column C.  We don't need to worry about it....easily recreated on my end.
2. When you put a number in A, it autosorts it.  The Task in B will show up in column H but only once.  So you may input several of the same Tasks and it will show up only once with the appropriate Areas on the Horizontal Axis and the single task (same name and # on the vertical column in H.  The appropriate RACSI code is also displayed (which shows a single task with multiple codes), from J to AF (probably will never be that large.  It all happens with the macros in the background located in Module1.  So just to be clear, the sorting happens in the worksheet VBA space and the placement to output happens in Module1.
3. The only way to invalidate the output in H:AF is to either "Reset" the model (see Reset button) or delete rows A:E and shift them up. -- this is a major reason for moving the output to another sheet.

Hope that helps.

B

How do you run the macro "Populate RASC" (is there a button on the worksheet for that that I don't see?)  I take it that its THIS macro that actually populates anything in the output, correct?
And is Reset supposed to delete the entire table, including formulas?

Before we throw the baby out with the bathwater, re: output (there's conditional formatting and some diagnosis to do, but not impossible) let's look at one alternative that might work for you.

Does the RESET button REALLY invalidate the model?  If the output were on another sheet, wouldn't RESET just do the same thing?

On the deleting of rows, we could handle with a trap, to ensure that only columns A:E are impacted by a delete.  Would this be a viable alternative to try?  The solution would be much faster in this case, unless you are planning other things that would help to mess up the OUTPUT.

Dave

Dave,

The Populate RASCI is used in the Sheet VBA about 3/4 the way down the Private Sub Worksheet_Activate() routine.  

I had not noticed the error in the Reset but you are correct.  It does not reset properly.  The reset button should simply clear the contents of A:E.  This needs to be fixed......sorry.

I'm ok with a trap and leaving the output table on the same worksheet if that's easier and cleaner.  

Thank you,

B.

Bright - I do think moving to a separate OUTPUT tab is wise, and the changes were not that difficult.  Please take a look at this.

PS - still don't understand how your initiate the "Populate RASC" macro

Here's a work in progress - see attached.

Dave
RASCI-Sheetv76-S-R2.xlsm

I think I fixed RESET.
I don't see how you TRIGGER the macro to Populate RASC.  Do you have a button for that?  Looks like it should be added...

Dave

Dave,

I think Rorya gave me that snippit of code to do it.  But if you can't figure it out.....well, I must say, it must be simply "magic"......;-)

There is no button to populate the RASCI Table; it simply happens when you add the info and tab/return to the next line.  I see where it is in the code; it seems to me that it is triggered automatically when the macro sees a change occur in the input side.

I'll take a look at your WIP now.  Much thanks.

B.

Not magic.  I was looking for a button to do it.  Its probably in you sheet change code.  Now woth an output tab we can relocate it at the next opportunity

Dave,

Still see some errors in testing but think you are on the right track here.  Thanks for the effort; I know this is a tougher one then normal.

B.

Share with me what you see and I'll handle accordingly.

Dave

Dave,

Greetings from "afar" -- Here's what I have;

I tested the code and most of what needs to be handled is potentially cosmetic except for an error I get when deleting a row or changing the copied data in one of the cells that has the same number.

1. When adding a row by putting in an existing number, The cursor should go to the first open cell in the row; making it easy to enter the next piece of data.

2. When adding a row by putting in a subset number (e.g. 2.1 after 2), the outline collapses; it should remain open instead of making you "Clear Groups" in order to enter additional information.

3. How do I delete a single row? When I do, it gives me a debug error. When you delete a row and then add another one, when you tab over to the Name field, I get a debug error (Runtime error 13 - Mismatch).     showX = Application.WorksheetFunction.RoundDown(Target.Offset(-1, 0).Cells(1, 1).Value, 0) 'round to parent value

4. Can we have a trap that prevents someone from modifying a copied cell?  That is, when you add another task with the same number and you go and change the text, you have the same problem as to having two different tasks occupying the same priority number.

In testing it, I also came up with three additional fields I need on the Input Screen.  Nothing has to be done with them now except when Reset, they too would need to be cleared.  Is there a way to add three Columns after the RASCI code column?  If not, I'll ask it as a related question.

Much thanks,

B.

RASCI-Sheetv76-S-R3-Test.xlsm

>>1. When adding a row by putting in an existing number, The cursor should go to the first open cell in the row; making it easy to enter the next piece of data.

I tried that - went to the bottom of the list and typed 1, then 2, then 2.2.  All the attempts positioned the cursor in Column B, at the first instance of that number.  PS - was this something that worked differently before?  Or, was this impacted by moving the output to the output tab?

REPAIR:  If its collapsed, will stay in that mode, otherwise will stay in expand mode

>>2. When adding a row by putting in a subset number (e.g. 2.1 after 2), the outline collapses; it should remain open instead of making you "Clear Groups" in order to enter additional information.

While the outline collapses, you still have the cursor positioned in column B, at the first instance of that subset.  I don't see the need to "clear groups" to enter data for that new item.  Is this different/impacted by moving the output to the output tab?

REPAIR:  If its collapsed, will stay in that mode, otherwise will stay in expand mode

>>3. How do I delete a single row? When I do, it gives me a debug error. When you delete a row and then add another one, when you tab over to the Name field, I get a debug error (Runtime error 13 - Mismatch).     showX = Application.WorksheetFunction.RoundDown(Target.Offset(-1, 0).Cells(1, 1).Value, 0) 'round to parent value

I deleted and added rows, with no errors.  A bit confused here...  Just select the rownumber, click with right mouse button and hit DELETE. I put error handling around it anyway, though I couldn't duplicate the error (and this is important - being able to duplicate errors, otherwise these error handling traps can be a bit blind / mask a logic issue).

>>4. Can we have a trap that prevents someone from modifying a copied cell?  That is, when you add another task with the same number and you go and change the text, you have the same problem as to having two different tasks occupying the same priority number.
Yes - but let's focus on dealing with the issues, first.

>>In testing it, I also came up with three additional fields I need on the Input Screen.  Nothing has to be done with them now except when Reset, they too would need to be cleared.  Is there a way to add three Columns after the RASCI code column?  If not, I'll ask it as a related question.
We can deal with that in this question, though it is expansion of scope - no problem.  Let's focus on dealing with the issues/bugs first, as you see them, before making any additions/modifications...

Let's get signoff on 1-3, then we can talk about the last 2 items...

See attached - I did a bit of cleanup in the RASCI worksheet code page.  Lot of fingers in the pie, so hopefully this helps the next person :)

So - beat it up and let me know what's not working.  Let's make this error free, then we can proceed.  I did quite a bit of testing, but you use it differently than I do as well - I'm off to dinner!

Dave


RASCI-Sheetv76-S-R3-Test.xlsm

Got it.  On my way out the door.  Will check back in in about 6 hrs.  

TY

Dave,

This is much better!  However, if you go in and delete two rows by highlighting the rows, right click and delete, then add another line by putting in a number (sub --- such as 2.5), it no longer auto sorts and it also messes up the outlining when you click on Iterate Col. A or Clear Groups.  See if you can replicate that problem.........

B.

which two rows did you delete in that example, please?

My bad on the delete (I corrected - I had put in a gracefulExit label to go to on subroutine exit, to turn events back on, however I didn't use it, so it exited the sub without turning events back on - at that point, none of the automated stuff would work from then).

here's the correction

Dave
RASCI-Sheetv76-S-R3-Test.xlsm

email me (see my profile).  I have some options to discuss with your other question, if you continue to have difficulties.

Dave

I went ahead and added the condition to prevent different process/task entry on same ID.  I noticed some redundant code that I cleaned up as well...

See attached and will check messages in the AM!

Dave
RASCI-Sheetv76-S-R3-Test-r1.xlsm

Great work on a tough project!  Much appreciate the hard (and smart) work on this.

"Thank you"!

B.