Generate chart from drop-down list of values

**karakahva** · 05-04-2010, 10:27 AM

Hi,

I am trying to generate a chart which would have:
- On the X axis, the "parameter": all the values included in a drop-down list (created with data validation)
- On the Y axis, the "results": the values taken by another cell (the content of the cell changes when the parameter is changed from the drop-down list).

In the attachment, please make as if the "SupportSheet" worksheet did not exist, and assume you only have the "Chart" worksheet. My issue is that the result is generated from a series of complex calculations from a different file, so it is not trivial to create a table with all the values to be charted.

I am using Excel 2002 (sic), but could get hold of a more recent version if needed.

Thanks in advance for your help!

**teylyn** · 05-05-2010, 12:20 AM

k,

I'm not sure I understand what you want to do. You show a line chart, instructing to forget about the Support sheet, but you only have one x and one y value on the chart sheet. So, where is the data for the chart supposed to be coming from? Even if it is created with complex calculations, it has to be somewhere in a table format for the chart to draw the values.

I think this needs a bit more background.

cheers

**karakahva** · 05-05-2010, 04:21 AM

Hi Teylyn,

Thank you very much for your reply.

I attach a new sample file, closer to what I'm doing. I'm trying to do a forecast of some estimates in the next few years, for different scenarios. While before we only needed to know the results in a specific year (see "Results" sheet: when you change the year in the drop-down menu, the results change), now we need to graph the change throughout the years. Hence the question: is it possible from such a sheet to chart the results for all the years?

I understand that I could change the approach and restructure the model in order to generate tables with the result trends per year (and maybe have the scenario in the drop-down menu), but this would take a major effort (the model in the sample file is very much simplified compared to the actual one); that's why I hoped there was an easy way to generate the chart from the drop-down menu (year in the X axis and corresponding result in the Y axis). But if there isn't, I'll just have to restructure the model I guess.

Thanks a lot!

K.

**teylyn** · 05-05-2010, 05:30 AM

Oioioi!

This is indeed a complex operation. The bottom line is: if you want to chart the data, you'll need the data in a table in a sheet.

Maybe someone more versed in VBA than I am can come up with a UDF that can do the calculations and write the results into a table that feeds the chart.

Other than that, I think remodelling is you next best option.

Sorry.

**karakahva** · 05-05-2010, 05:54 AM

Hi Teylyn,

Thank you again for your reply. Unfortunately, that's what I feared after looking around in the Net and not finding any solution. I don't know VBA at all, so I hoped there would be a different way to do it (as learning VBA would probably take me more time than remodeling).

Anyway, thanks again and I welcome any further advice!

K.

**Andy Pope** · 05-05-2010, 12:02 PM

This will create a matrix of the 6 scenarios for all years.

Please Login or Register  to view this content.

**karakahva** · 05-09-2010, 07:56 AM

Wow!

Thanks Andy...

I've been offline for a few days, but I'll test it at once...

**karakahva** · 05-13-2010, 06:36 AM

Hi all,

I stumbled in an easier solution for my problem, and I wanted to post it here for the record, hoping it will be useful to someone in the future.

I was looking for a way to do sensitivity analysis, and I found that it's quite easy to do "What-If" analysis in Excel. Basically you just have to create a table with all the potential values for your input cell, then with the Data/Table (or Data/What-If Analysis/Data Table) function it's possible to generate all the results corresponding to the different inputs. This way you can actually generate a table from a drop-down list of values, and from there it's immediate to make a graph.

The method is better explained in the following URL:
http://www.treeplan.com/chapters/02_...71029_1042.pdf

I also attach my "solved" second sample file.

Big thanks to everybody who helped!