Dynamic cell ranges in charts
 

Hi,

I have set up a number of named cell ranges which plot ok in the chart, but
now I want make global changes in the cell ranges without having to modify
them in the "Refers To" box. I am using Excel 2007. Can I use Named cells
within the Refers To box, ie. calling the new max and min cells and
establishing the range that way?

Dynamic cell ranges in charts
 

Of course. For example, take Shane's suggestion from your last post, and
replace various parameters with cell addresses or names:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$100) ,1)

becomes for example

=OFFSET(Sheet1!$A$1,Sheet1!$C$1,Sheet1!$D$1,Sheet1 !$F$1,Sheet1!$G$1)
=OFFSET(Sheet1!$A$1,Sheet1!RowOffset,Sheet1!ColOff set,Sheet1!NumRows,Sheet1!NumCols)
etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Harold Christian" wrote in
message ...
Hi,

I have set up a number of named cell ranges which plot ok in the chart,
but
now I want make global changes in the cell ranges without having to modify
them in the "Refers To" box. I am using Excel 2007. Can I use Named cells
within the Refers To box, ie. calling the new max and min cells and
establishing the range that way?

Dynamic cell ranges in charts
 

Thanks Jon,

Helpful comments. But what I really am looking to do is use manually-entered
numeric values stored in a series of cells which describe the start and end
points of the arrays (which need to change from time to time). Then in my
Names I would like to insert these cell values into the range specification,
thereby redefining the arrays as simply as possible. It looks like I cannot
call or refer to those numeric cell values and have Excel recognize them as
cell range values. I'm sure there must be a way. I am not a programmer or I
bet this would be easy to do.

Harold

"Jon Peltier" wrote:

Of course. For example, take Shane's suggestion from your last post, and
replace various parameters with cell addresses or names:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$100) ,1)

becomes for example

=OFFSET(Sheet1!$A$1,Sheet1!$C$1,Sheet1!$D$1,Sheet1 !$F$1,Sheet1!$G$1)
=OFFSET(Sheet1!$A$1,Sheet1!RowOffset,Sheet1!ColOff set,Sheet1!NumRows,Sheet1!NumCols)
etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Harold Christian" wrote in
message ...
Hi,

I have set up a number of named cell ranges which plot ok in the chart,
but
now I want make global changes in the cell ranges without having to modify
them in the "Refers To" box. I am using Excel 2007. Can I use Named cells
within the Refers To box, ie. calling the new max and min cells and
establishing the range that way?

Dynamic cell ranges in charts
 

You want to place the values or the cell references into the name
definitions?

This is for the designer, not the user? In that case, hide the cells where
the user will never find them, or use

=OFFSET(Sheet1!$A$1,RowOffset,ColOffset,NumRows,Nu mCols)

where RowOffset, ColOffset, NumRows, and NumCols have refers to formulas
like =1, =5, etc. Change these refers to formulas when necessary.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______



"Harold Christian" wrote in
message ...
Thanks Jon,

Helpful comments. But what I really am looking to do is use
manually-entered
numeric values stored in a series of cells which describe the start and
end
points of the arrays (which need to change from time to time). Then in my
Names I would like to insert these cell values into the range
specification,
thereby redefining the arrays as simply as possible. It looks like I
cannot
call or refer to those numeric cell values and have Excel recognize them
as
cell range values. I'm sure there must be a way. I am not a programmer or
I
bet this would be easy to do.

Harold

"Jon Peltier" wrote:

Of course. For example, take Shane's suggestion from your last post, and
replace various parameters with cell addresses or names:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$100) ,1)

becomes for example

=OFFSET(Sheet1!$A$1,Sheet1!$C$1,Sheet1!$D$1,Sheet1 !$F$1,Sheet1!$G$1)
=OFFSET(Sheet1!$A$1,Sheet1!RowOffset,Sheet1!ColOff set,Sheet1!NumRows,Sheet1!NumCols)
etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Harold Christian" wrote in
message ...
Hi,

I have set up a number of named cell ranges which plot ok in the chart,
but
now I want make global changes in the cell ranges without having to
modify
them in the "Refers To" box. I am using Excel 2007. Can I use Named
cells
within the Refers To box, ie. calling the new max and min cells and
establishing the range that way?

Dynamic cell ranges in charts
 

Jon,

Thanks! That worked for me.

Harold

"Jon Peltier" wrote:

You want to place the values or the cell references into the name
definitions?

This is for the designer, not the user? In that case, hide the cells where
the user will never find them, or use

=OFFSET(Sheet1!$A$1,RowOffset,ColOffset,NumRows,Nu mCols)

where RowOffset, ColOffset, NumRows, and NumCols have refers to formulas
like =1, =5, etc. Change these refers to formulas when necessary.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______



"Harold Christian" wrote in
message ...
Thanks Jon,

Helpful comments. But what I really am looking to do is use
manually-entered
numeric values stored in a series of cells which describe the start and
end
points of the arrays (which need to change from time to time). Then in my
Names I would like to insert these cell values into the range
specification,
thereby redefining the arrays as simply as possible. It looks like I
cannot
call or refer to those numeric cell values and have Excel recognize them
as
cell range values. I'm sure there must be a way. I am not a programmer or
I
bet this would be easy to do.

Harold

"Jon Peltier" wrote:

Of course. For example, take Shane's suggestion from your last post, and
replace various parameters with cell addresses or names:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$100) ,1)

becomes for example

=OFFSET(Sheet1!$A$1,Sheet1!$C$1,Sheet1!$D$1,Sheet1 !$F$1,Sheet1!$G$1)
=OFFSET(Sheet1!$A$1,Sheet1!RowOffset,Sheet1!ColOff set,Sheet1!NumRows,Sheet1!NumCols)
etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Harold Christian" wrote in
message ...
Hi,

I have set up a number of named cell ranges which plot ok in the chart,
but
now I want make global changes in the cell ranges without having to
modify
them in the "Refers To" box. I am using Excel 2007. Can I use Named
cells
within the Refers To box, ie. calling the new max and min cells and
establishing the range that way?