Both things are possible. I've posted a sample workbook he

http://peltiertech.com/Sample/DynamicIndirectCharts.zip

I don't know what's wrong with your ChartCategories refers to formula, I
copied and pasted it directly from your post, unless there's a problem with
Sheet2!$P$8.

Cell P10 should correctly reference the range with an exclamation point:
'Sheet1'!F2:K2

The single quotes are unnecessary for these simple sheet names, but Excel
ignores (and removes) them.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
oups.com...
I'm trying to set up a dynamic Data Source. I've read Jon Peltier's
example of creating a named range with offset, but for some reason
Excel says my named range is invalid:

Here's what I've defined:

ChartCategories
=OFFSET('Sheet1'!$F$2,0,0,1,'Sheet2'!$P$8)

Sheet1 F2 up to Q2 contains the categories
Sheet2 P8 contains how many categories (cols) I want to include.

Can someone help me?!

Thanks.

John

P.S. I also have the entire category range referenced in another cell
(i.e. P10 is 'Sheet1'F2:K2) if there is some way to use INDIRECT to
specify the range based on the text in P10 (which there doesn't seem
to be).