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.
