Adding data to chart

rutgerterhaar

New Member
Joined
Jan 21, 2012
Messages
18
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi rutgerterhaar,

Assuming that your data is similar to:
Sheet1
HTML:
	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
Code:
=Sheet1!$B$1:$F$1
then in B3 to B7 the rows header
In C3
Code:
=HLOOKUP($A$1,Sheet1!$A$1:$F$6,2)
In C4
Code:
=HLOOKUP($A$1,Sheet1!$A$1:$F$6,3)
In C5
Code:
=HLOOKUP($A$1,Sheet1!$A$1:$F$6,4)
ect, change the last value to fit the row number....

HTML:
col3		
		
	data1	3
	data2	6
	data3	4
	data4	3
	data5	5
HTML:
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

New Member
Joined
Jan 21, 2012
Messages
18
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Yes a VBA will be able to help you out.

pls modify formula in C3 from:
Code:
=HLOOKUP($A$1,Sheet1!$A$1:$F$6,2)

into:
Code:
=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:
Code:
=IF(ISBLANK(Sheet1!A2),"",Sheet1!A2)

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

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028

ADVERTISEMENT

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

New Member
Joined
Jan 21, 2012
Messages
18
Thanks, this worked fine!! I've made some adjustments, but the code worked fine!

Thanks for the replies!!!


Greetz Rutger
 

rutgerterhaar

New Member
Joined
Jan 21, 2012
Messages
18
Thanks, this worked fine!! I've made some adjustments, but the code worked fine!

Thanks for the replies!!!


Greetz Rutger
 

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,605
Members
414,393
Latest member
Vignesh Mechz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top