Creating graph(s) from list-dependent dynamic data....

Hi All,

What I would like to accomplish is to graph 1-3 data sets on the same graph (w/ fixed scale), with the data sets chosen by two drop-down lists. The first list selects a "material", the second chooses an "experiment" which was performed on the material. The chosen selections of the two lists will point to a unique data set, which feeds into the graph. The size of the matrix for materials/experiments is 3x3 at this point, but will grow over time as we add data.

I found this thread, which seems to be close to the last half of what I need (identifying and plotting the data set): http://www.excelforum.com/excel-gene...f-formula.html

I have attached a sample workbook to help visualize. As always, thanks in advance for the help.

sample.xlsx

Hi badtangent,

Welcome to the forums.

I rearranged your data and then created an offset formula to find the products from your lists.

You can learn more about offset and charts with this step-by-step tutorial and video demonstration:
Case Study - Creating a Dynamic Chart in Excel Using Offset Formula


Here is your sample file redone with a chart. All you need to do is to add new experiments to the right with a header like you see in each column and then pick your poison:

Chart Experiment Picker.xlsx

Let me know if this helps.

Steve=True

Hi Steve=True,

Thanks for the help. I don't think I'm communicating what I want very clearly. Now that you have taken a look at the sample file, I'll try again. I have a number of users who will be using this sheet. To make usage as simple as possible, I would like them to only have to use the Graphs tab. If I have to manipulate/restructure the Lists or Data tabs for this purpose, that's OK. The sample I provided has a 3x3 data matrix, but in actuality it is more like 16x16 (and growing). For this reason, you can understand why I don't want them wading through an ever-expanding Data sheet to select data ranges....

I can't think of another way to make the data selection more simple than what is on the Graph tab. I am no VB pro, but in the past I have kludged large Booleans to make selections based on multiple independent lists - that really sucked. As the data set continues to grow, this approach does not seem sustainable to me, and I have assumed that there is a more elegant (and simpler) way to accomplish this (through the participation and input of people in this forum who are smarter than me). Maybe in the end, there just isn't an easier way.

The Graphs tab is where all the user action and graphing should take place, and eventually I will lock/hide the Lists and Data tabs from users.

I hope I have clarified.

Thanks again,
badtangent