Using the indirect function in a chart or series f

Question

I am attempting to reference a defined named range within a chart, that part works fine. However, I am also attempting to have that range change via user input.

For example:

In a dropdown I have:
Apples
Grapes
Oranges

Of which the user can select one. All three are named ranges.

Then I have a chart like this one:

The chart references the named range like so:

SERIES(Base!$AQ$1,Test.xlsx!Dates,Test.xlsx!Apples,1)

However, when I use the Indirect function to point the Apples portion of this forumla to my dropdown, it does not work:

SERIES(Base!$AQ$1,Test.xlsx!Dates,INDIRECT("Test.xlsx!"&'BaseSheet'!$C$10),1)

C10 being the dropdown cell, and BaseSheet being the sheet where both these cells exist. The reference for th name doesn't really matter, but it's another part of the workbook.

Answer 1

You can't put an Indirect() function into the chart series function.

Instead, you need to create the range reference with a named formula in the Name Manager, then plug it into the chart using the syntax

='sheet name'!Range_Name

EDIT: Or with an Indirect function

=INDIRECT("Test.xlsx!"&'BaseSheet'!$C$10)