Trying to make chart interactive, it works but only kind of

[Butcher1]

In the attached file I have an interactive data range where I can pull up various stats and the desired time period and a chart that displays a single stats. In A21 I have this formula,
=OFFSET(INDEX(Chart_Options,MATCH(A19,Chart_Options,0)),0,1,1,(15-COUNTBLANK(B5:P5)))
which I have used to created a defined name of input, which I have put in the chart series for source data.

My problem is, if I change the desired charted stat in A19 the chart goes blank and I get an error message saying, "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference." Putting the cursor in A21, being in edit mode and hitting enter will then update the chart and fix the issue.

Where did I make a mistake, I can't see it for myself?

edit: I can't edit any part of the drop down menu without getting an error message.

[lcartwright]

Only briefly looking at it, but on your Summary Tab Cell A21, highlight formula then Ctrl+Shift+Enter

[lcartwright]

Charting.xlsx

Think i found it, (what i did):

On summary tab cell B19 used this formula: =VLOOKUP($A$19,$A$6:$L$16,2,0) and copied across (changing the 2 - column reference or as i did just put column numbers above and cell referenced them), then coloured it in white so not seen.
I also lengthened out the chart data to incorporate cells B19:L19

Also as a bit of a play:

Added another secondary plot on your chart to take in your original cell reference and changed it to scatter chart.


Have fun

[Butcher1]

Thank you, I hadn't thought to do it that way.

[Butcher1]

I am marking this thread as unsolved since unfortunately I couldn't hide the helper formulas and still have it work. I also tried this formula,
=INDEX(B6:P16,MATCH(A19,Chart_Options,0),1):INDEX(B6:P16,MATCH(A19,Chart_Options,0),15-COUNTBLANK(B5:P5))
and got some intermittent success with it. Whenever I changed cell a19 I got an error message. Opening the source data window would fix it, I wouldn't have to do anything with the source data, just open the window and then close it. For all the searching I have done on this subject, I am really surprised I haven't found anyone else that has done it. Is it even possible without VBA?

[humdingaling]

you cannot hide data you want in a chart

your named range "input" was incorrect

i have changed it to read

Please Login or Register  to view this content.

ie...you were missing the $

[Butcher1]

Thank you. So there can not be unlocked references in the defined name that is used for source data?

[humdingaling]

it gives unpredictable outcomes which is why it would work sometimes
unsure why you unlocked references in your example though