20 rows but need to display only 2 at a time in a chart.

[Roshlin]

Hi friends,

I have a chart with 20 rows and columns. I need to compare 2 rows at a time
in a simple line chart to see thier progress, for example row 1 with row 5 ,
then 4 with 10 and then 1 with 16 etc.

what would be the easiest way to achive this, for now I have to do it by
selecting the rows each time by edit data.

Can I have 2 dropdown menus above the chart with the column names so that
the chart would display the values I chose.

Thanks for your help.
Roshlin

 

[Dave Curtis]

Hi,

It's easy to achieve this if you have a couple of combo boxes, each
referencing your list of series names/headings (say in column A.)

Then use the OFFSET function to generate the data for each of the series and
use these two rows as the basis of the chart.

Suppose your data is in rows 2 to 21, with some sort of heading in column A.

In A23, set up a combobox, and format so the input range is $A$2:$A$21, the
cell link is $A$23, and the dropdown lines is 20.
Do another one in A24, linking to that cell.

In B23, enter =OFFSET($A$1,$A$23,COLUMN()-1) and drag to the right as far as
necessary.
In B24, enter =OFFSET($A$1,$A$24,COLUMN()-1) and drag to the right.

Then if you use the comboboxes to choose two series of data, the relevant
numbers should appear in rows 23 and 24.
Construct your chart from these two rows. Choosing new series from the
comboboxes shpould update your chart.

Hope this helps.

Dave

url:http://www.ureader.com/msg/10297394.aspx

 

[Roshlin]

Hi Dave,

Thanks for your quick response.
I'm done half way, I have the 2 combo boxes in A23 and A24 and they are
working fine.
I've made the series names with the column A.

where do I put this "the cell link is $A$23, and the dropdown lines is 20."

I get error Name? when I do this "In B23, enter
=OFFSET($A$1,$A$23,COLUMN()-1) "

Thanks for your help

 

[Dave Curtis]

Hi,
If you right click on the combobox, you should get a menu appear.
On the Control tab, make the input range $A$1:$A$20 or whatever your range
is, enter $A$23 or$A$24 for the cell link, and change the drop down lines to
however many rows you have.

The cell link causes a number to appear in A23 (orA24) indicating the number
of the item in the list which you've chosen with the combobox.

Try again, and I'm not sure why you get a Name? error.

Come back if you need more.

Dave

url:http://www.ureader.com/msg/10297394.aspx

 

[Roshlin]

Hi Dave,
When I right click the combo box I get the normal menu, nothing about the
combo box, just in case, I have made the combobox using the data validation
method, guess that's the right way. I'm using Excel 2007, just to inform.


The combo boxes are working correctly, only I need them to give me the data,
so that I can make the chart.
Please help.

I'm posting here in ureader because your reply has not appeared in the
microsoft forum.

Thanks

url:http://www.ureader.com/msg/10297394.aspx

 

[Dave Curtis]

Hi,

That's why it's not working then. You need to create the combobox from the
Forms toolbar. Create the comboboxes from here, and you can drag them around
and resize them to cover the cell you designate as the cell link. If you
right-click on this, you will get the options I described earlier.
My company has, perhaps wisely, decided not to upgrade to Excel 2007, so I'm
still using 2003, but you should still be able to do the same thing.
I thought ureader was the forum. Where else should I post replies?
I'm happy to email you the spreadsheet I use with this technique.

Dave

url:http://www.ureader.com/msg/10297394.aspx

 

[Roshlin]

Hi Dave,
Sorry of troubling you again.
OK now I have the combobox with the form tool and it is working, but the
conection formula =OFFSET($A$1,$A$24,COLUMN()-1) is still giving the same
error= name?

You sugested you could email me the sheet, if posible please do it roshlin
AT yahoo DOT com

Just to inform you my first post was in the microsoft discussions page
that's why I was surprised that you your reply was here and not there
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.
public.excel.charting&lang=en&cr=US


thanks agian

url:http://www.ureader.com/msg/10297394.aspx