Problems Using Form Controls for a Chart in Excel

Occasional Visitor

Hi everyone,

 

I'm trying to create an interactive chart in Excel. I've followed all the steps in this YouTube video:

https://www.youtube.com/watch?v=QWJQZLGQ4Gw.

 

I have two form controls: a scroll bar and a spin button. And I defined names for formulas to toggle which rows of my table are selected. (I feel like this sounds vague but watching the video might help).

 

I connected my scroll bar to a cell I named "Scroll_Bar" and my spin button to a cell I named "Number_Displayed". I then defined a name,

Lives_Alone = INDIRECT("AD"&Scroll_Value&":AD"&Scroll_Value+Number_Displayed).

This way, based on how scroll bar and the spin button, the end user can decide which subset of the data in that column they want. I know this part works fine because when I scroll or click on the spin button, the highlighted cells change accordingly. 

 

The problem arises when I want my chart to only represent the highlighted cells. I created a bar chart and I'm trying to adjust the data series for it using my defined name. That is, after right-clicking on my chart and selecting "Select Data", I tried to redefine one of my series. I tried changing "Series values" from

=Dashboard!$AD$2:$AD$9

to

=Dashboard!Lives_Alone

However. this error pops up (attached). Like I said, I know there's nothing wrong with the formula itself because when I click on it in my name manager, it toggles properly and selects the right rows. I just don't understand why Excel won't allow me use this formula for my chart.

 

Any help will be appreciated. It's only a very small fraction of a project I'm working on for school, I've spent far more hours out of my day than I would've preferred trying to figure this out. 

FormControls #DynamicChartsIn this video you will learn how to create a fully dynamic chart using Excel form controls and Name Manager. We have created dyna...
0 Replies