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,
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
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.