Graphic to Plot Results from a Dynamic Table

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
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
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
Bright01Author Commented:
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
Bright01Author Commented:
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.
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

dlmilleCommented:
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.
Bright01Author Commented:
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.
dlmilleCommented:
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
Bright01Author Commented:
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.
dlmilleCommented:
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
Bright01Author Commented:
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.
dlmilleCommented:
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
dlmilleCommented:
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
Bright01Author Commented:
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.
Bright01Author Commented:
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.
dlmilleCommented:
Ok - give me a workbook with a couple scenario tabs so I can finalize.

Dave
dlmilleCommented:
Never Mind - I'll just make a copy of Scenario_Template
dlmilleCommented:
Ok.  Please try it out.

Dave
Workbench-v27-r2.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
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.
dlmilleCommented:
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
Bright01Author Commented:
Very good 1st gen. version.  I'm certain I'll be back with another post when I incorporate this graphic.  

Much thanks,

B.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.