Interactive Excel Chart

[westma]

I have collected curves for as many different brands of a single product on the market as possible, and I have each one on a separate excel worksheet named for the corresponding product (i.e. a workbook named Hoover with a different vaccuum on each sheet, a Kirby workbook, etc).

My problem is that I need to be able to easily plot any number of these curves on a single graph in a different workbook for comparison purposes.

Would it be possible to have the names of each of these vaccuums (corresponding to the names of each sheet) in one column, with an embedded chart that would plot the curve or curves when the user selected the name? I had heard that a Selection_Change event would work for this, but I have no idea how to write the code.

 

[SkipVought]

westma,

One of the mistakes that Excel users make is to put SIMILAR DATA in SEPARATE WORKSHEETS -- BIG PROBLEM!

What you need is ONE sheet with ALL your data in a single table. Then you are able to do what you want to do quite simply.

You will have to add one column for Product (your sheet names)



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[westma]

OK, so if I were to put all my similar data into a single table on ONE sheet, it would have to be laid out as follows:


SPEED Product1Product2Product3
[m/s] [MPH]
0.0 0.0 0 0 0
0.5 1.1 0 0 0
1.0 2.2 0 0 0
1.5 3.4 0 0 0
2.0 4.5 0 0 0
2.5 5.6 0 0 0
3.0 6.7 0 0 0
3.5 7.8 0 0 0
4.0 8.9 36 25 0
4.5 10.1 66 47 37
5.0 11.2 104 80 74
5.5 12.3 150 119 138
6.0 13.4 205 165 202
6.5 14.5 269 218 279
7.0 15.7 344 281 356
7.5 16.8 428 351 466
8.0 17.9 528 433 576

Then how do I get an embedded chart to plot the data when I highlight the cell with its corresponding name?

 

[Marko24]

Not really....

You can put it this way

Speed (km/H) Speed MpH
Product 1 xx xx
Product 2 xx xx
Product 3 xx xx

OKie?

 

[SkipVought]

westma,

It looks like you might need 2 additional columns...
Brand & Product



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[shaggi]

Hi Westma,
I have recently had to do something similar for work, having several departments, and plotting each ones' work processed over a period of time.Using data validation, you choose a department and their work processed is plotted. I used the If(And) formula, ie:
A B C Q R S T U
1 DEP1 DEP2 DEP3 DATE PROCESSED
2 10 20 30 1/1 10
3 20 35 45 2/1 20
4 15 14 2 3/1 15
5 12 1 9 4/1 12
6
7
8 ENTER DEP DEP1

In cell c8 would be the validation, the formula in cell r2 would be: if(and(c8="DEP1&quot,A2,if(and(c8="DEP2&quot,B2,if(and(c8="DEP3&quot,C2,"NO DEP CHOSEN&quot))

Create the graph from columns Q & R

For more than 5 choices, you would have to choose another cell at the end instead of "NO DEP CHOSEN" and start again with the if(and)s!!(S2 in the above case.)


Adrian

 

[westma]

I don't think it really matters how I lay out my table, because that is not my question. Here is my question:

Given:
Cell A1 contains the words "ProductA"; cells A2:A65536 contain data that can be graphed

Cell B1 contains the words "ProductB"; cells B2:B65536 contain data that that can be graphed

Cell C1 contains the words "ProductC"; cells C2:CB65536 contain data that that can be graphed

Cell D1 contains the words "ProductD"; cells D265536 contain data that that can be graphed

Also Given: An embedded line graph

Problem: I would like for a user to be able to highlight Cell A1 and have the graph automatically plot the data in cells A2:A65536, or I would like for a user to be able to highlight Cell A1 AND cell C1 and have the graph automatically plot the data in cells A2:A65536 AND cells C2:C65536 so that the user can compare the two curves, or if there is no cell selected, there is no plot displayed on the graph.

It WOULD be simple to just display all the data. It would also be pretty simple to just change the source data for the values I want displayed whenever I want them displayed. But since there will be about 30 columns in my table,I don't want all the data to be displayed all the time, and it would be MUCH SIMPLER for the user if he/she could just select the name of the graph they want to see. So. . .

Question: Is there someone who can help me with the code for making this happen if it is even possible (which I have been told a Selection_Change event may work) since I am VBA illiterate.

I apologize for all of the confusion, and thank you for your help.

 

[shaggi]

Hi again,
still think you can do this without VBA but if you do then maybe "SELECT CASE" is the route you should be going down and use "TARGET" to activate this. All seems a bit complicated when it can (probably) be done within worksheet functions. It would be possible to have two different variables in "a1" and "c1" & use worksheet functions to put the underlying data into a graph, would be easier to explain with the raw data, other than that I am lost

sorry
Adrian

 

[westma]

Hi shaggi,
Ya thanks for the info. I posted my previous before seeing your message. I will play with it and see what happens.

Rgds,
matt

 

[SkipVought]

All you have to do is apply an AutoFilter. THe user selects the product in the AutoFilter AND you have the Option setting to Plot Visible Cells ONLY.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com