Hello,

I have a spreadsheet with about 10 columns of data. The number of rows can
vary but its ussually less than 20. I've got a set of 'index' fields that
define which data is in which column and what range of rows I want to act
on.

Then I've defined some named ranges that vary based on the contents of the
index fields. For example:

Stats!TotalC==OFFSET(INDIRECT(stats!$O$3&FIXED(sta ts!$P$2,0,0)),0,0,stats!
$Q$2-stats!$P$2+1,1)

where O3 contains the column name, P2 is the first row, and P3 is the last
row I want to consider.

I've also defined them as follows:
Stats!Th=INDIRECT(stats!$O$6&FIXED(stats!$P$2,0,-1)&":"&stats!$O$6&FIXED
(stats!$Q$2,0,-1))

I've tested and used these named ranges in functions and they work
properly, but I can't use them in a chart for some reason. If I define them
as above, with the sheetname then I simply end up with no data. If I don't
include the sheetname then I get a message that there is a problem with the
function ('one or more invalid references' );

I'm sure this can be done, but I've been banging my head on it for a while
already. Can anyone point me in the right direction?

I'm using Excel 2003.

Thanks

Marc Pelletier
Goldak Airborne Surveys

Hi,

I'll try to get back to you later, but for now try replacing the sheet
references with workbook names (without sheets names)

--
Thanks,
Shane Devenshire


"Marc Pelletier" wrote:

Hello,

I have a spreadsheet with about 10 columns of data. The number of rows can
vary but its ussually less than 20. I've got a set of 'index' fields that
define which data is in which column and what range of rows I want to act
on.

Then I've defined some named ranges that vary based on the contents of the
index fields. For example:

Stats!TotalC==OFFSET(INDIRECT(stats!$O$3&FIXED(sta ts!$P$2,0,0)),0,0,stats!
$Q$2-stats!$P$2+1,1)

where O3 contains the column name, P2 is the first row, and P3 is the last
row I want to consider.

I've also defined them as follows:
Stats!Th=INDIRECT(stats!$O$6&FIXED(stats!$P$2,0,-1)&":"&stats!$O$6&FIXED
(stats!$Q$2,0,-1))

I've tested and used these named ranges in functions and they work
properly, but I can't use them in a chart for some reason. If I define them
as above, with the sheetname then I simply end up with no data. If I don't
include the sheetname then I get a message that there is a problem with the
function ('one or more invalid references' );

I'm sure this can be done, but I've been banging my head on it for a while
already. Can anyone point me in the right direction?

I'm using Excel 2003.

Thanks

Marc Pelletier
Goldak Airborne Surveys

Marc,

I've just started using named ranges in charts. One thing I've seen is that
I can't just enter the named range. First I enter a normal range, e.g.,
MySheet!$A$1:$A$5 and then I go back and replace the $A$1:$A$5 with the
named range. Or at least that's what I think I did

I'm not sure if that addresses your problem, but it's been 12 hours since
you posted so figured it was worth a shot.

Doug

"Marc Pelletier" wrote in message
...
Hello,

I have a spreadsheet with about 10 columns of data. The number of rows can
vary but its ussually less than 20. I've got a set of 'index' fields that
define which data is in which column and what range of rows I want to act
on.

Then I've defined some named ranges that vary based on the contents of the
index fields. For example:

Stats!TotalC==OFFSET(INDIRECT(stats!$O$3&FIXED(sta ts!$P$2,0,0)),0,0,stats!
$Q$2-stats!$P$2+1,1)

where O3 contains the column name, P2 is the first row, and P3 is the last
row I want to consider.

I've also defined them as follows:
Stats!Th=INDIRECT(stats!$O$6&FIXED(stats!$P$2,0,-1)&":"&stats!$O$6&FIXED
(stats!$Q$2,0,-1))

I've tested and used these named ranges in functions and they work
properly, but I can't use them in a chart for some reason. If I define
them
as above, with the sheetname then I simply end up with no data. If I don't
include the sheetname then I get a message that there is a problem with
the
function ('one or more invalid references' );

I'm sure this can be done, but I've been banging my head on it for a while
already. Can anyone point me in the right direction?

I'm using Excel 2003.

Thanks

Marc Pelletier
Goldak Airborne Surveys

?B?U2hhbmVEZXZlbnNoaXJl?=
wrote in news:0C7B9675-EF88-
:

I'll try to get back to you later, but for now try replacing the sheet
references with workbook names (without sheets names)



Thanks, Shane, but it won't let me do that. Unless I give it a sheet name
it complains, and when I do give it a sheet name it behaves the same as
before and the workbook reference is removed by excel.

cheers

Marc

"Doug Glancy" wrote in
:

I've just started using named ranges in charts. One thing I've seen
is that I can't just enter the named range. First I enter a normal
range, e.g., MySheet!$A$1:$A$5 and then I go back and replace the
$A$1:$A$5 with the named range. Or at least that's what I think I did


Doug, that's pretty much what I'm trying to do. I'm modifying an existing
chart. Actually I'm destroying it over and over!

cheers

Marc