Solved

Graphic to Plot Results from a Dynamic Table

Posted on 2012-03-13
19
155 Views
Last Modified: 2012-03-15
EE Professionals,

I have a nice WS that, when you input the Time Horizon, it auto generates the number of months considered in the financial analysis.  Additionally, I can add a new Use Case with another macro (button in left corner of WS).

Here's the challenge.  How do I set up another WS that shows the graphical results of both a chart that may change dimensions and concurrently, may add additional tables (new Use Cases)?

I'm looking for ideas on this.

Thank you in advance,

B.
Workbench-v26.xlsm
0
Comment
Question by:Bright01
  • 10
  • 9
19 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37715508
Bright - can you create a starting point chart?  "A chart" is relatively generic and I can think of any number of things to chart that aren't what you want.

Cheers,

Dave
0
 

Author Comment

by:Bright01
ID: 37715646
Dave,

Thanks for jumping in. Attached is an example of the graphic.  Would really like a two axis graphic with one series line and the other bar.  And it would be great if it could include the specific Business Scenario (as you add them or clear them) and the associated ROI calculations.  I put  them outside the graphic to show you.....but really they should be incorporated into the graphic as a title or result image.  May not be "doable".  That's why I was looking for ideas.

Hope this helps.  Let me know if you have other questions... I think the hard part here is actually how do you get it to generate the graphic based on the number of scenarios and the dynamic nature of the Time Horizons (which may be different by scenario).

Thanks again,

B.
Workbench-v27.xlsm
0
 

Author Comment

by:Bright01
ID: 37715846
Dave,

I've been giving this some thought and here's what I'm thinking;  It may be very complicated to plot out ALL of the data points in a dynamic WS.  What if we were to create a WS that's a single template and then add the ability to select a specific "Scenario", regardless of how many you have, but when you select it (button?), it plots the dynamic range in that particular scenario into the template Graphic WS.  

Does that make sense?  It's slightly limiting since you'd have to do only one at a time....but I think the other way (all the Scenarios) may be too complex.

B.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 42

Expert Comment

by:dlmille
ID: 37716613
IMHO, the only way to plot all (or a set) scenarios is to sum all cash flows (assuming the scenarios can be combined) and have one line.  Otherwise, there's too much on the chart for comprehension.
0
 

Author Comment

by:Bright01
ID: 37716675
Agree.  However, understand, each Scenario (originally rows 3:5) would be it's own graphic; and because you could have multiple Scenarios (by adding Use Cases) this would be very complex.

So again, perhaps by building a single WS Graphic Template, with some sort of control/check box, that allows you to take that single string of data (the Cashflows) and plot it against the template?  That way when you replicate another "Use Case" with the same control, you could click it and it would auto plot into the template one at a time.  Error checking would mean that you could only have one graphic produced at a time but you would have the option of unchecking one and checking another to get another graph plotted.

If that doesn't make sense, this one may be too difficult to make simple.

Thoughts?

B.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37716791
Ok - create some real data and give this a shot selecting from one to another scenario.  How close did I get?

Dave
Workbench-v27.xlsm
0
 

Author Comment

by:Bright01
ID: 37716919
Real close! However, now add another Use Case (button on the upper left); you will see that is copies the one above (by the way, it should clear it so it has no data in the copy (but that's another posting)...... so now how do you create another graphic?   The only way I thought of was to have a check box that would only allow one check box active at a time (regardless of WS) and when checked, it would plot those flows associated with that Case only.

Make sense?

B.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37716938
I modified so the DV list would allow selection of more scenarios.

Just select the scenario/business case from L5 on the ROIGraph sheet.

Dave
Workbench-v27.xlsm
0
 

Author Comment

by:Bright01
ID: 37717232
You've almost got this!  Let me see if I can further clarify this.

1.) The Model allows you to add as many Scenarios as you like (it copies the entire Scenario_Template to a new Tab and numbers it Scenario1, Scenario2, Scenario3, etc.).  This is not part of the model I have sent you. But you may recall this post.

2.) Then within each Scenario, you can add as many Use Cases as you like (it copies the top Use Case from rows 3:5 all the way out).  That's on the current Scenario_Template with the button at the left.  

So your approach, to have a single ROI Graphic where you can select the Scenario (Tab) and any of the Use Cases that may be created, would work!  What would have to happen is that a macro would have to look at the Scenario# Tabs and log the Tabs in a compiled list that would show up in the selection box.  It would also have to look at each Scenario# Tabs and log each of the Use Cases in a separate list.  By selecting the Scenario and the Use Case, it could plot any Case.

I still think this is very complex to ask for ........ you let me know when you think we're done with this one.

Thank you,

B.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37717267
When you want to see a graph, you push the button on a given Scenario# tab, right?  That button could populate the sheet tab name in the ROIGraph tab (Range M7), and create the dynamic range for the pulldown at L5.

Alternatively, in the ROI Graph tab, you could select the Scenario sheet name, and then the Use Case.

What do you think?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37717366
Try this.  There's a couple macros in the ROI Graph Sheet's CodePage.

You have two drop downs in L4:L5.  Use this to add a couple additional Scenario sheets.  THEN, when you go to ROI Graph, select the Scenario Tab you want (which will auto-load the DV in the Scenario drop down) and then select scenario.  After having done that, your chart should look fantastic.

PS - you'll get a warning popup if the tab/scenario combination doesn't give the chart the right data.

See attached.

Dave
Workbench-v27-r1.xlsm
0
 

Author Comment

by:Bright01
ID: 37717370
I think that would work.  However, for the dynamic range at L5, it's really "Use_Case", not Scenario (my mistake). However, I like the way you come at this from both the Scenario Sheet to generate an ROIGraph tab (I assume it would be ROIGraph1, ROIGraph2, ROIGraph3, etc.); and the Graph Sheet allow you to pick a Use_Case would be great!.  I think that would work well.

That would be great!

B.
0
 

Author Comment

by:Bright01
ID: 37717386
Dave,

This is really good!  One more turn of the crank --

1.) The Macro needs to select ONLY the Scenario with a number (this is because there are other sheets with Scenario NAME but the created/replicated Scenario all are Scenario#.

2.) Change Scenario on the Graphic Sheet to Use_Case with a # based on how many I have created.

Looking good.


B.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37717424
Ok - give me a workbook with a couple scenario tabs so I can finalize.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37717427
Never Mind - I'll just make a copy of Scenario_Template
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37717459
Ok.  Please try it out.

Dave
Workbench-v27-r2.xlsm
0
 

Author Comment

by:Bright01
ID: 37717504
OK...tested it.  Where you have Selection of "USE_CASE" on the Graph Tab, it is looking for the USE_CASE, in each individual Scenario (Scenario1, Scenario2, etc.); not the Scenario_Template.  That is because within each Scenario Tab, I can have a different number of CASES, as well as different values in each case.

When I then clear the WB, it deletes all of the WSs that are labeled Scenario#; and we start all over again, adding Scenarios and Use_Cases.

Make sense?

B.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37717540
Yep - that's correct.  The ROIGraph logic looks at everything from scratch, every time - finding scenario tabs, re: Scenario#, and once selected, sets the range name to find all use cases for that next drop down, looking on that scenario tab.  If you're using DynamicDV! the list will be sorted, and without blanks.

Dave
0
 

Author Closing Comment

by:Bright01
ID: 37724438
Very good 1st gen. version.  I'm certain I'll be back with another post when I incorporate this graphic.  

Much thanks,

B.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

820 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