named dynamic range that ends with first text entry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've found many example on how to created a dynamic named range which ends at
the last numeric or text entry. I would like to create one that ends at the
first text entry.

The data I'm workign with has a heading, then numeric data, then another
heading, then more numeric data. I only need the numeric data between the
first heading and the second, and the amount of rows between frequently
changes.

Is this possible?

Thank you, super-geniuses.
 
Hi!

One way:

Assume your first header is in A1 followed by several rows of numbers and
then another header.

=OFFSET(A2,,,MATCH("*",A2:A100,0))

Biff
 
Depending on how you intend to use this range you might want to use this
instead:

=OFFSET(A2,,,MATCH("*",A2:A100,0)-1)

Biff
 
Perfect. Thank you!

Biff said:
Depending on how you intend to use this range you might want to use this
instead:

=OFFSET(A2,,,MATCH("*",A2:A100,0)-1)

Biff
Click to expand...
 
Biff - FWIW,

"*" includes nulls ( "" - zero length strings) in it's match criteria in
addition to "Text".
"*?" will look at *only* "Text" (alpha or numeric).
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLOOKUP, a table with text and numerics 7
Dependent drop down dynamic range formula, off sheet. 7
Calculate MEDIAN of Last x Rows in a Column 6
Help to adapt Formula syntax to work with Dynamic Named Ranges 13
SUMPRODUCT - Tweak 2
Dynamic Named Range formula without using OFFSET function 5
Conditional formatting on a dynamic range - workaround 1
Help with my Excel database 4

Back
Top