Offset function help

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I need help using OFFSET to define a named range that
constitutes the 3 most recent (last rows) in a larger
range that tracks trend data, so my chart based on the
named range displays the three most recent months of data
based on the underlying range having new months added
monthly.

For exmaple, A2:A20 is a range of ascending month names.
B2:B20 is the monthly availability value. In the first
month only row 2 will be filled. In the second month,
I'll add values for row 3, and so on up to but never more
than Row 20 in month 20. Let's further assume that until
I enter the month's data, nothing is entered in column A
or B for a row.

There is a means to define my named Range, let's
say "Dates" to be the bottom three non-empty rows in the
range A2:A20. If I have a similar named Range for
Availabilty, I can then plot a bar chart that showns only
the latest (bottom most) three non-blank rows of
availability data in the range A2:B20.

Try as I might, I cannot get OFFEST to do this, despite
following a KB example.

Any ideas would be appreciated. Dan
 
Dan,

There are a lot of pitfalls to using dynamic named ranges. Another
way is to use a three cells that have formulas that extract the values
you need. For example, these three array formulas, each entered
separately and using Ctrl-Shift-Enter, will pull the last three values
entered in A1:A20:

=INDIRECT("A" & LARGE(ROW(A1:A20)*(A1:A20<>""),3))
=INDIRECT("A" & LARGE(ROW(A1:A20)*(A1:A20<>""),2))
=INDIRECT("A" & LARGE(ROW(A1:A20)*(A1:A20<>""),1))

Enter them in three cells, and those cells can be used for your chart.

HTH,
Bernie
MS Excel MVP
 
Back
Top