Dynamic chart using a listbox

**bergtho** · 09-02-2010, 05:57 PM

Working on creating a dynamic chart using a listbox.

Read about this, steps are:
1) Create named ranges for labels and data - Did this OK
2) Create and format chart OK - Did this OK
3) Base chart on named ranges - Not sure how to do this
4) Create listbox - Did this OK

Something important is missing, not sure what.

Sample file attached.

**teylyn** · 09-02-2010, 06:23 PM

re 1) You created static range names. The trick is to create dynamic range names that change their contents depending on the value selected from the list box.

re 4) You need to link the listbox with a cell, for example Q1, so the number of the listbox choice is represented in a cell and can be used for the dynamic range name.

back to 1) create two dynamic range names
ChtName =OFFSET(Test!$B$3,Test!$Q$3,0)
ChtData =OFFSET(Test!$C$3:$F$3,Test!$Q$3,0)

re 3) edit the chart's source data and enter

Series name =Test!ChtName
Series values =Test!ChtData

Now select a different value from the list box. See how the number in Q3 changes. See how the chart changes.

Oh, and please drop the 3D chart junk. It only makes the columns harder to interpret and compare.

See attached.