Do I really need OFFSET?
 

I have defined a Dynamic Name as follows:

=OFFSET(Sheet1!$A$4,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)

The label for the data is in A3, the data starts in A5 and continues
downward. The definition above does start plotting from A5 but adds a blank
cell. There is always one extra empty data point that is plotted. Is there
an easy fix so that the plot starts at A5 and stops at the last data point?

Thanks.

Do I really need OFFSET?
 

See whether changing -1 to -2 (two places) fixes the range definition. If
so, you have two non-blank cells that you don't want to count instead of
one. One is for a label atop the data in column A, the other would be a cell
above that with other contents.

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


"bflorox" wrote in message
...
I have defined a Dynamic Name as follows:

=OFFSET(Sheet1!$A$4,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)

The label for the data is in A3, the data starts in A5 and continues
downward. The definition above does start plotting from A5 but adds a
blank
cell. There is always one extra empty data point that is plotted. Is
there
an easy fix so that the plot starts at A5 and stops at the last data
point?

Thanks.

Do I really need OFFSET?
 

Changing to -2 does eliminate the extra blank data point; although there is
only one, not two, blanks between the label and the data. Could COUNTA have
been used without OFFSET and be told where to start from somehow?

Thanks.

"Jon Peltier" wrote:

See whether changing -1 to -2 (two places) fixes the range definition. If
so, you have two non-blank cells that you don't want to count instead of
one. One is for a label atop the data in column A, the other would be a cell
above that with other contents.

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


"bflorox" wrote in message
...
I have defined a Dynamic Name as follows:

=OFFSET(Sheet1!$A$4,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)

The label for the data is in A3, the data starts in A5 and continues
downward. The definition above does start plotting from A5 but adds a
blank
cell. There is always one extra empty data point that is plotted. Is
there
an easy fix so that the plot starts at A5 and stops at the last data
point?

Thanks.

Do I really need OFFSET?
 

1. You need either OFFSET or INDEX or something similar.

2. Remove extraneous blank rows and columns within the data set.
there is only one, not two, blanks between the label and the data.


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



"bflorox" wrote in message
...
Changing to -2 does eliminate the extra blank data point; although there
is
only one, not two, blanks between the label and the data. Could COUNTA
have
been used without OFFSET and be told where to start from somehow?

Thanks.

"Jon Peltier" wrote:

See whether changing -1 to -2 (two places) fixes the range definition. If
so, you have two non-blank cells that you don't want to count instead of
one. One is for a label atop the data in column A, the other would be a
cell
above that with other contents.

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


"bflorox" wrote in message
...
I have defined a Dynamic Name as follows:

=OFFSET(Sheet1!$A$4,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)

The label for the data is in A3, the data starts in A5 and continues
downward. The definition above does start plotting from A5 but adds a
blank
cell. There is always one extra empty data point that is plotted. Is
there
an easy fix so that the plot starts at A5 and stops at the last data
point?

Thanks.