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?

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

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!

Bingo! The problem was not having the workbook reference before the
named ranges. If I add the sheet name, it converts to the workbook
name as you suspected.

(And the alternative way using INDIRECT in your example works well,
too.)

I didn't know you had to prefix named ranges in formulas with the
workbook name. You'd think Excel would automatically assume the named
range would be in the active workbook. (???)

Thanks, Jon!

John

Excel automatically assumes the name in the formula is on the same worksheet
as the formula, but a chart series formula is not in a worksheet, it's in a
chart. So for a chart you always need to insert the sheet reference for a
range of cells or a sheet or workbook name for a range name.

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


wrote in message
oups.com...
Bingo! The problem was not having the workbook reference before the
named ranges. If I add the sheet name, it converts to the workbook
name as you suspected.

(And the alternative way using INDIRECT in your example works well,
too.)

I didn't know you had to prefix named ranges in formulas with the
workbook name. You'd think Excel would automatically assume the named
range would be in the active workbook. (???)

Thanks, Jon!

John

That makes sense. I thought when you define a new name it applies to
the whole workbook? That's why you need to specify the sheetname in
the name formula.

Are names sheet-specific so whatever sheet is active when you define
the name is the sheet that the name will be associated with?

Thanks.

John

If you don't specify, the name is workbook-specific, but this doesn't help
the chart to find the name. To make a name worksheet-specific, you have to
name it with the sheet name as a prefix:

'My Sheet'!RangeName

The prefix is lost after the name is entered, but you can still see it
listed to the right of the name in the listbox in the Define Names dialog.

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


wrote in message
oups.com...
That makes sense. I thought when you define a new name it applies to
the whole workbook? That's why you need to specify the sheetname in
the name formula.

Are names sheet-specific so whatever sheet is active when you define
the name is the sheet that the name will be associated with?

Thanks.

John