Adding data to chart

[rutgerterhaar]

Hi there,

I was trying to add data to a chart with vba, but I can not make it work. This is my problem:

In sheet1 I have a table with a dynamic number of rows and columns. In sheet2 I have a chart (2d line) and a comboBox. The input for the combobox are the columns headers in sheet1 (column1, column2,...). The comboBox works fine and give in cell A1 the column number.
I want to write a code that adds data from sheet1 according to the columns number in the chart. So if I select in the comboBox column1, the chart has to show the data from sheet1, column1. If I select another column in the comboBox, the first data series in the chart have to be deleted en the new selection has to be shown.

I hope that the problem is clear.

Lots of thanks for your help (and understanding my crappy English)

Greetz Rutger

 

[cyrilbrd]

Hi rutgerterhaar,

Assuming that your data is similar to:
Sheet1

	col1	col2	col3	col4	col5
data1	3	4	3	6	2
data2	5	2	6	5	2
data3	2	5	4	4	5
data4	2	4	3	4	5
data5	4	6	5	6	3

Then in Sheet2:
In cell A1 I place a data validation List with the range

=Sheet1!$B$1:$F$1

then in B3 to B7 the rows header
In C3

=HLOOKUP($A$1,Sheet1!$A$1:$F$6,2)

In C4

=HLOOKUP($A$1,Sheet1!$A$1:$F$6,3)

In C5

=HLOOKUP($A$1,Sheet1!$A$1:$F$6,4)

ect, change the last value to fit the row number....

col3		
		
	data1	3
	data2	6
	data3	4
	data4	3
	data5	5

col3		
		
	data1	=HLOOKUP($A$1,Sheet1!$A$1:$F$6,2)
	data2	=HLOOKUP($A$1,Sheet1!$A$1:$F$6,3)
	data3	=HLOOKUP($A$1,Sheet1!$A$1:$F$6,4)
	data4	=HLOOKUP($A$1,Sheet1!$A$1:$F$6,5)
	data5	=HLOOKUP($A$1,Sheet1!$A$1:$F$6,6)

You can't however hide column B and C, but you can relocate them in such a way not to be seen (under the chart...)

Hope this helps.

 

[rutgerterhaar]

Hi!

Cyrilbrd, thanks for the help!!!

This is what I mean to do, but the only problem is that the data is variable, so there could up to 100 columns en 2000 rows. So then I would have to make I lot of formulas. But the general idea is right.

I was looking for a VBA code which looks for the maximum row and column. And which, depending on the value (a columns name) of the combobox, puts de data from that column automatically in a chart and deletes it when i select an other column in de combobox.

Maybe someone knows how to do this!!

Thanks!!

Rutger

 

[cyrilbrd]

Yes a VBA will be able to help you out.

pls modify formula in C3 from:

=HLOOKUP($A$1,Sheet1!$A$1:$F$6,2)

into:

=IF(ISBLANK(Sheet1!A2),"",HLOOKUP($A$1,Sheet1!$A:$F,(IF(ISBLANK(Sheet1!A2),"",ROW(Sheet1!A2)))))

just copy paste down...
same in C2, just place this:

=IF(ISBLANK(Sheet1!A2),"",Sheet1!A2)

While waiting for someone to assit you with a better solution.

 

[tusharm]

There's no need for code. The solution below works with any number of rows and any number of columns in Sheet1.

I started the table in Sheet1 with header values in row 2. A2: X, B2:Y1, C2:Y2.

In A3:C20 enter numbers of interest. column A could have text, if necessary.

Now, define a named formula (Formulas tab | Defined Names group | Name Manager button). Also, ensure that the scope of the name is 'Workbook'.

YHdrs =OFFSET(Sheet1!$B$2,0,0,1,COUNTA(Sheet1!$2:$2)-1)


Next, in Sheet2, in cell B2 create a data validation dropdown (Data tab | Data Tools group | Data Validation button). In the Allow dropdown select List and in the source enter =YHdrs.

In C2 enter the formula =MATCH(B2,Sheet1!2:2,0)

Now, create 2 named formulas (ensure that the scope of the names is Sheet2):

XVals =OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A)-1,1)
YVals =OFFSET(Sheet2!XVals,0,Sheet2!$C$2-1)

Create a chart with XVals as the x values and YVals as the y values. The chart series formula should look like =SERIES(,Sheet2!XVals,Sheet2!YVals,1)

For more on how to use named formulas in charts see
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/
particularly the linked page
Names in Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/names_in_charts.html

That's it. Add as many rows and/or columns to Sheet1 and the set up in Sheet2 will automatically adjust.

Hi there,

I was trying to add data to a chart with vba, but I can not make it work. This is my problem:

In sheet1 I have a table with a dynamic number of rows and columns. In sheet2 I have a chart (2d line) and a comboBox. The input for the combobox are the columns headers in sheet1 (column1, column2,...). The comboBox works fine and give in cell A1 the column number.
I want to write a code that adds data from sheet1 according to the columns number in the chart. So if I select in the comboBox column1, the chart has to show the data from sheet1, column1. If I select another column in the comboBox, the first data series in the chart have to be deleted en the new selection has to be shown.

I hope that the problem is clear.

Lots of thanks for your help (and understanding my crappy English)

Greetz Rutger

 

[rutgerterhaar]

Thanks, this worked fine!! I've made some adjustments, but the code worked fine!

Thanks for the replies!!!


Greetz Rutger

 

[rutgerterhaar]

Thanks, this worked fine!! I've made some adjustments, but the code worked fine!

Thanks for the replies!!!


Greetz Rutger