Comparing charts dynamically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a worksheet to have 2 charts on them. The first uses information
from the main worksheet to pull in averages from 15 different worksheets. It
also has a combo box to select the item (column) they are trying to average
from 20 different columns. This one works fine. What I am trying to do with
the second chart is using a Name range select a worksheet using the same
column from the first combo box so the user can compare one specific area
versus overall averages. Here is the info I have for the first combo box and
chart so it can be updated.

XValues=OFFSET('CORP DATA'!$B$4,0,0,COUNTA('CORP DATA'!$B:$C)-1,2)
YValues=OFFSET('CORP DATA'!$D$4,0,Charts!$E$4-1,COUNTA('CORP
DATA'!$D:$D)-2,-1)
WhichAverage=OFFSET(Combined.xls!YValues,-1,0,1,1)

My question is how do I use a Name range of different worksheets to populate
the second chart using the info above?

In other words when I select a column from the first combo box it populates
the first chart. When I select a name from the second combo box I want to
use the info from the first and second to populate the second chart. I
realize I am asking a lot but is there anone who assist me on this or at
least point me in the right direction. Many thanks for any assistance.
 
Ok I did a Vlookup to populate a cell using the actual names of the
worksheets on the charts worksheet. I believe I need to use ADDRESS for the
formulas. However, I am sure I need to use the address function, but really
unsure how incorporate it into the OFFSETs.
 
I tried to work on this, however I am still unable to make it happen. M2 is
the cell which is populated with the worksheet name. Can anyone assist me on
this?

YValues=OFFSET(ADDRESS($D$4,0,Charts!$E$4-1,COUNTA(ADDRESS($D:$D,,,,Charts!$m$2))-2,-1,Charts!$m$2))

XValues=OFFSET(ADDRESS(Charts!$B$4,0,0,COUNTA(ADDRESS($B:$C,,,,Charts!$m$2))-1,2,Charts!$m$2))
 
Fysh -

I think the best way is to name each range on its own sheet, using a sheet level
name. This means the name of the name (sorry) is prefixed with the sheet name:

Sheet1!XValues
Sheet1!YValues

In your Chart sheet, set aside a range at least as long as the longest of the
individual sheet's named ranges. These can have the workbook level names XValues and
YValues. With the sheet name in M2, as you describe, select the XValues range in the
Chart worksheet, type this into the formula bar

=indirect(m2&"!XValues")

and hold CTRL+SHIFT while pressing Enter. This creates an array formula which brings
in all the values from the XValues name in the sheet named in M2. If that sheet's
XValues range is shorter than in the Chart sheet, the bottom of Chart!XValues will
contain #N/A values, which the chart will happily ignore. Repeat for the Y values,
and chart a series using the XValues and YValue from the Chart worksheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top