Excel use Offset to create a dynamic range in a chart

I have created a named range 'Values' to select values for a chart using the following


If I press F5 and select Values the correct range is highlighted i.e. 12 columns to the left of the reference Admin!$B$4&62

If I try to use the formula in the Series Values I get the error 'That function isn't valid'
If I try to use the range name in the Series Values I get the generic 'The formula you typed contains an error' etc

Any ideas what I am missing?
Paul GAsked:
Who is Participating?
SteveConnect With a Mentor Commented:
OK missing sheet ref from INDIRECT reference...


See attached...
OK.. are you using Named Range for the offset to sit in?

If yes.. then go to the chart range... select edit... drop all but the sheet ref up to "!" and then press F3... select the named range there and all should be OK.

To set up a named range... use formulas > name manager... and then new using the offset formula.

If you could post the workbook it would be easy enough to fix it for you.
Paul GAuthor Commented:
If I press F3 and select the range I still get the error.

See attached.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.