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

OFFSET(INDIRECT(Admin!$B$4&62),0,0,1,-12)

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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
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.
0
Paul GAuthor Commented:
If I press F3 and select the range I still get the error.

See attached.

Thanks.
Test-Sheet.xlsm
0
SteveCommented:
OK missing sheet ref from INDIRECT reference...

=OFFSET(INDIRECT("Product!"&Admin!$B$4&62),0,0,1,-12)

See attached...
Test-Sheet.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.