Need Dynamic Data source help

[robotman]

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

 

[Barb Reinhardt]

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.

 

[robotman]

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!

 

[Barb Reinhardt]

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.

 

[robotman]

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

Any other ideas?

 

[Jon Peltier]

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

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]

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,'Sheet1'!$F$4:$Q$4,1)

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

- Jon

 

[Barb Reinhardt]

Thanks Jon. I should have caught that.

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,'Sheet1'!$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
_______

 

[robotman]

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

 

[Jon Peltier]

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

 

[robotman]

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

 

[Jon Peltier]

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