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).

Let's break this down. You have the following offset equation:

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

The reference cell is Sheet1$F$2.
You want to go 0 rows down and 0 rows to the right of the reference
The range is one row high.
WHat value do you have in Sheet2!$P$8? If it's <1, you'll get an error.

" wrote:

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).

Named Range "ChartMonths":

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

Yes, your description is correct. Reference starts in Sheet1$F$2. I
don't want any row or col offset and the data is just 1 row (i.e. $F
$2:$Q$2). The value in Sheet2!$P$8 is 1 to 12 depending on how many
months I want to show.

For the chart, I use the following Series formula:
=SERIES('Sheet1'!$E$4,ChartMonths,'Sheet1'!$F$4:$Q $4,1)

This gets an "A formula in this worksheet contains one or more invalid
references..."

Thanks for any help!

I presume your named range "ChartMonths" is a workbook named range as opposed
to a Worksheet named range. Try CTRL G and enter "ChartMonths" and see what
it shows you. I'm thinking your offset isn't right.

" wrote:

Named Range "ChartMonths":

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

Yes, your description is correct. Reference starts in Sheet1$F$2. I
don't want any row or col offset and the data is just 1 row (i.e. $F
$2:$Q$2). The value in Sheet2!$P$8 is 1 to 12 depending on how many
months I want to show.

For the chart, I use the following Series formula:
=SERIES('Sheet1'!$E$4,ChartMonths,'Sheet1'!$F$4:$Q $4,1)

This gets an "A formula in this worksheet contains one or more invalid
references..."

Thanks for any help!

Hmmm... nope. I hit Control-G and enter "ChartMonths" and it properly
selects the selected range of months.

Any other ideas?

The chart doesn't know where to find the name ChartMonths. Try one of these:

=SERIES('Sheet1'!$E$4,'Sheet1'!ChartMonths,'Sheet1 '!$F$4:$Q$4,1)
=SERIES('Sheet1'!$E$4,Book1.xls!ChartMonths,'Sheet 1'!$F$4:$Q$4,1)

If you use the first, it will probably be converted to the second.

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


"Barb Reinhardt" wrote in message
...
I presume your named range "ChartMonths" is a workbook named range as
opposed
to a Worksheet named range. Try CTRL G and enter "ChartMonths" and see
what
it shows you. I'm thinking your offset isn't right.

" wrote:

Named Range "ChartMonths":

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

Yes, your description is correct. Reference starts in Sheet1$F$2. I
don't want any row or col offset and the data is just 1 row (i.e. $F
$2:$Q$2). The value in Sheet2!$P$8 is 1 to 12 depending on how many
months I want to show.

For the chart, I use the following Series formula:
=SERIES('Sheet1'!$E$4,ChartMonths,'Sheet1'!$F$4:$Q $4,1)

This gets an "A formula in this worksheet contains one or more invalid
references..."

Thanks for any help!

Thanks Jon. I should have caught that.
"Jon Peltier" wrote:

The chart doesn't know where to find the name ChartMonths. Try one of these:

=SERIES('Sheet1'!$E$4,'Sheet1'!ChartMonths,'Sheet1 '!$F$4:$Q$4,1)
=SERIES('Sheet1'!$E$4,Book1.xls!ChartMonths,'Sheet 1'!$F$4:$Q$4,1)

If you use the first, it will probably be converted to the second.

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


"Barb Reinhardt" wrote in message
...
I presume your named range "ChartMonths" is a workbook named range as
opposed
to a Worksheet named range. Try CTRL G and enter "ChartMonths" and see
what
it shows you. I'm thinking your offset isn't right.

" wrote:

Named Range "ChartMonths":

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

Yes, your description is correct. Reference starts in Sheet1$F$2. I
don't want any row or col offset and the data is just 1 row (i.e. $F
$2:$Q$2). The value in Sheet2!$P$8 is 1 to 12 depending on how many
months I want to show.

For the chart, I use the following Series formula:
=SERIES('Sheet1'!$E$4,ChartMonths,'Sheet1'!$F$4:$Q $4,1)

This gets an "A formula in this worksheet contains one or more invalid
references..."

Thanks for any help!

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).