Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Moving the Output to another Worksheet

Posted on 2011-10-03
23
Medium Priority
?
299 Views
Last Modified: 2012-05-12
EE Professionals,

Thanks to many of you, we have a great little Workbook that allows you to identify Tasks, put them in priority order and manage responsibilities for a particular Project.  The RASCI workbook attached has two parts (both input and output) on a single worksheet.  A task is added when you put in a number in column A as where a task fits in priority. A macro automatically evaluates the number and places it in the appropriate order.  Since a task can have multiple assignments, if a number is repeated, the macro automatically copies the task (so as not to allow two tasks to occupy a single priority).  The input can be viewed by sub category as well as shown in total by pressing the "Iterate" or "Clear" buttons.  If you press the Table button, you will be moved to column H where THE OUTPUT is placed and shown.  

What I'd like to do is move the output to a different worksheet in order to simply the model.  Right now, I believe that as rows are added and deleted, it impacts the output table so moving it to a different Tab would have positive impact.

Much thanks in advance.  Please let me know if this is a mult-question request due to its complexity.  

B.
Copy-of-Copy-of-RASCI-Sheetv76-S.xlsm
0
Comment
Question by:Bright01
  • 14
  • 9
23 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36906596
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
0
 

Author Comment

by:Bright01
ID: 36906623
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36906668
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Bright01
ID: 36906775
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36907042
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?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36907085
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
0
 

Author Comment

by:Bright01
ID: 36907138
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36907142
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36907146
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
0
 

Author Comment

by:Bright01
ID: 36907204
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36907429
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

0
 

Author Comment

by:Bright01
ID: 36913079
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36913374
Share with me what you see and I'll handle accordingly.

Dave
0
 

Author Comment

by:Bright01
ID: 36913729
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36914469
>>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
0
 

Author Comment

by:Bright01
ID: 36914533
Got it.  On my way out the door.  Will check back in in about 6 hrs.  

TY
0
 

Author Comment

by:Bright01
ID: 36914621
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36914637
which two rows did you delete in that example, please?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36914644
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
0
 
LVL 42

Expert Comment

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

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36914750
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
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 36915070
Here's the final posting on your entire request - adding 3 columns has been done, as well as the prevent re-entry of a path item with different task/process, though you can use delete to clear what's been entered, then start again...

Cheers,

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

Author Closing Comment

by:Bright01
ID: 36915175
Great work on a tough project!  Much appreciate the hard (and smart) work on this.

"Thank you"!

B.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

564 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