Return last visible row

[Tod]

In column A is a range of successive dates. In column B is
a formula that uses the associated date in A to return a
value from a set of data. The formula returns "" if the
associated date is greater than the maximum date in the
set of data. The end result is like this:

Date Value
8/1/2003 18
8/2/2003 21
8/3/2003 7
8/4/2003 47
8/5/2003 86
8/6/2003
8/7/2003
8/8/2003
etc....

The set of data only goes to August 5th, so the values are
only filled in to that date. From the 6th down, the
formula returns "".

Okay, I also have a line chart of this. I want the x Axis
to show the entire date range. But I only want to chart
the range of values that are present. I'm thinking that
the way to do that is have a formula that either returns
the cell location of the last value or the range of
values. I could then use the resulting range to put in the
series.

Am I making sense?

tod

 

[dvt]

In column A is a range of successive dates. In column B is
a formula that uses the associated date in A to return a
value from a set of data. The formula returns "" if the
associated date is greater than the maximum date in the
set of data. The end result is like this:

Date Value
8/1/2003 18
8/2/2003 21
8/3/2003 7
8/4/2003 47
8/5/2003 86
8/6/2003
8/7/2003
8/8/2003
etc....

The set of data only goes to August 5th, so the values are
only filled in to that date. From the 6th down, the
formula returns "".

Okay, I also have a line chart of this. I want the x Axis
to show the entire date range. But I only want to chart
the range of values that are present. I'm thinking that
the way to do that is have a formula that either returns
the cell location of the last value or the range of
values. I could then use the resulting range to put in the
series.

I can think of two options for you. The easiest option is to use an XY
scatter chart instead of a line chart and have the formula return #N/A
instead of "". You can use your dates as the x-axis data. Use the function
NA() to return #N/A. If you don't like the looks of your sheet with all of
those #N/A's in it, use condition formatting to disguise the contents if the
formula returns #N/A. Use the ISNA function in the conditional formatting.

Another possibility is to use a dynamic chart. For detailed directions, see
Jon Peltier at
http://www.geocities.com/jonpeltier/Excel/Charts/Dynamics.html. You'll have
to adapt his method to count the number of cells according to your critieria
(i.e. <>"").

Dave
dvt at psu dot edu

 

[Jon Peltier]

Dave -

I can think of two options for you. The easiest option is to use an XY
scatter chart instead of a line chart and have the formula return #N/A

This will still work fine with a Line Chart.

- Jon