How can I dynamically graph from multiple data tables according to user selectio

[capcon]

Hello,

I have been trying to create a graph that will allow me to graph any particular table from upto ten tables. I have really nice individual line graphs but would love to be able to know how I can automate this.

So, I have;

[1] 10 data tables

[2] each table has 2 columns and 36 rows

[3] column-1 is ddd/mm

[4] column-2 is a 3 digit number


I've been trying to learn offset, index, match functions and named ranges which I believe should be able to accomplish the task but I am failing miserably (fairly new to excel)

Help!

Is anybody able to give me some guidence, it would be much appreciated.

Thanks

Tom

 

[stemelliott]

The problem (I think) you are having is that you have 10 data tables, which I assume are on different sheets.

in this case, you need a way to select the table where you looking for the information. one of the best ways to do this is by using the INDIRECT(...) function.

e.g. INDIRECT("sheet1!A1:B36") or if you have a drop down box with each sheet name in cell a1 you could use: INDIRECT($a$1&"!A1:B36") or using named range INDIRECT($A$1) if A1 contains a list of named ranges.


Then you can embed this in your index/offset functions as required.

 

[stemelliott]

BTW this is good for educating about match and INDEX

http://chandoo.org/wp/2010/11/09/2way-lookup-formulas/

 

[capcon]

Thanks Stemelliott

This will show my level of knowledge, all the data tables are on one sheet so I thought it would be nice and easy but I just have not got my head around the concepts yet.

Having said that, I am going to push on again but this time using the indirect function as well.

Thanks for the link too, will visit that first.

Tom