Dynamic range that stops at first blank row

[roscoe]

I have two ranges of data, separated by a number of blank rows. Is there a way to create a dynamic range that includes the top block but not the bottom block, i.e. it stops when it encounters a blank row?

 

[Aladin Akyurek]

Yea, I got that...but not what they are attempting to do



Everything else was correct.

In my experience, if I type the name of a dynamic range in the box to the left of the formula bar, it highlights the range. In this case if I type Data it renames the range to whatever is selected. That's odd to me and tells me something ain't right.

If you carry out the steps as stipulated, you won't be able to see Data
via the Name Box. Only way to see it is again thru Formulas | Name Manager.

To test what is in Data, enter the following in say J2:

=Data

Select J2, go to the Formula Bar, select what you see, and the function key F9. This action would show the contents of Data.

By the way, a better test is:

=COUNT(INDEX(Data,0,1))

This should count all the dates in the relevant area. If this returns 0,
the dates in column A on the relevant sheet are not true dates.

 

[T. Valko]

I have two ranges of data, separated by a number of blank rows. Is there a way to create a dynamic range that includes the top block but not the bottom block, i.e. it stops when it encounters a blank row?

Something like this should work...

=$A$2:INDEX($A$2:$A$100,MATCH(TRUE,$A$2:$A$100="",0))

Use a reasonable end of range A100.

 

[roscoe]

If you carry out the steps as stipulated, you won't be able to see Data
via the Name Box. Only way to see it is again thru Formulas | Name Manager.

To test what is in Data, enter the following in say J2:

=Data

Select J2, go to the Formula Bar, select what you see, and the function key F9. This action would show the contents of Data.

By the way, a better test is:

=COUNT(INDEX(Data,0,1))

This should count all the dates in the relevant area. If this returns 0,
the dates in column A on the relevant sheet are not true dates.

Sorry, but none of that worked for me...

 

[roscoe]

Something like this should work...

=$A$2:INDEX($A$2:$A$100,MATCH(TRUE,$A$2:$A$100="",0))

Use a reasonable end of range A100.

OK, so explain to me the "TRUE" as the first parameter in the MATCH function. That's supposed to be the lookup_value...and this didn't work either by the way. Nothing but error results.

And please explain "Use a reasonable end of range A100." I assume it needs to go past the bottom of the range? What if it goes into the next range?.

 

[T. Valko]

OK, so explain to me the "TRUE" as the first parameter in the MATCH function. That's supposed to be the lookup_value...and this didn't work either by the way. Nothing but error results.

And please explain "Use a reasonable end of range A100." I assume it needs to go past the bottom of the range? What if it goes into the next range?.

You said:

Originally Posted by roscoe
I have two ranges of data, separated by a number of blank rows. Is there a way to create a dynamic range that includes the top block but not the bottom block, i.e. it stops when it encounters a blank row?

Ok, let's assume this is your data:

Book1
	A
1	Header
2	1
3	1
4	2
5	2
6	3
7	3
8	4
9
10
11	10
12	10
13	10
14	10
15	10
16	10
17	10
18	10
19	10
20	10
Sheet1

The dynamic range for the first group of data is defined as:

=$A$2:INDEX($A$2:$A$100,MATCH(TRUE,$A$2:$A$100="",0))

MATCH(TRUE,$A$2:$A$100="",0) means: find the location of the first empty cell in the range A2:A100. The first empty cell in that range is A9.

So, the dynamic range is A2:A9.

Use a reasonable end of range A100 means: you didn't say how much data might be in the first group of data so use a reasonably sized range. If you know for certain that the first group of data will never more than 100 rows then you don't want to use 50000 as the end of that range. You'd use 100. Use the smallest sized range as you can. It doesn't matter if the end of range extends into the next group of data. The MATCH function will find the empty cell between the groups.

Try some formulas using the dynamic range:

=SUM(MyRange)
=COUNTIF(MyRange,1)
=COUNTIF(MyRange,10)
=MAX(MyRange)

 

[roscoe]

That worked...Cool!

But I still don't understand how the first parameter in a match function can be a Boolean and not a cell reference.

 

[T. Valko]

That worked...Cool!

Good deal!

But I still don't understand how the first parameter in a match function can be a Boolean and not a cell reference.

Yeah, the lookup value can be a boolean TRUE or FALSE.

 

[Aladin Akyurek]

Sorry, but none of that worked for me...

Re-cap, also just for the record...

BigNum:

=9.99999999999999E+307

Lrec:

=MATCH(BigNum,'401(k) Transactions'!$A:$A)

BlankRec:

=MATCH(TRUE,'401(k) Transactions'!$A$2:INDEX('401(k) Transactions'!$A:$A,Lrec+1)="",0)-1+ROW('401(k) Transactions'!$A$2)-1

After some debugging, Lrec is replaced with Lrec+1.

Data:

='401(k) Transactions'!$A$2:INDEX('401(k) Transactions'!$H:$H,BlankRec)

 

[roscoe]

Thanks for all your help, works great!