Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

It would if I knew the end cell, i.e. "C10", but I do not. I have no idea
which row the information will be entered. The only cell I know is the
starting cell, i.e. A3. The next cell could be A5 or A16.

Thanks

"Ron Coderre" wrote:

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

Yup...I understand, now.

See if this comes closer:

For a list of items (or blanks) in A1:A100, this formula returns the number
of contiguous non-blank cells, beginning with A1:

B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100)+NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)

If you wanted to start from A25, this is the form:
B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

It would if I knew the end cell, i.e. "C10", but I do not. I have no idea
which row the information will be entered. The only cell I know is the
starting cell, i.e. A3. The next cell could be A5 or A16.

Thanks

"Ron Coderre" wrote:

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

Something is wrong with the formula. If I enter any value between A2 to A100
the answer is always "0". If I enter a value into A1 the answer is
"9999999999".

Trying to disect the equation to see if it works.

Thanks,

Dan

"Ron Coderre" wrote:

Yup...I understand, now.

See if this comes closer:

For a list of items (or blanks) in A1:A100, this formula returns the number
of contiguous non-blank cells, beginning with A1:

B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100)+NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)

If you wanted to start from A25, this is the form:
B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

It would if I knew the end cell, i.e. "C10", but I do not. I have no idea
which row the information will be entered. The only cell I know is the
starting cell, i.e. A3. The next cell could be A5 or A16.

Thanks

"Ron Coderre" wrote:

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

Oops! I apologize..

B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)

That's an array formula and must be commited by holding down the [Ctrl] and
[Shift] keys when you press [Enter].

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Something is wrong with the formula. If I enter any value between A2 to A100
the answer is always "0". If I enter a value into A1 the answer is
"9999999999".

Trying to disect the equation to see if it works.

Thanks,

Dan

"Ron Coderre" wrote:

Yup...I understand, now.

See if this comes closer:

For a list of items (or blanks) in A1:A100, this formula returns the number
of contiguous non-blank cells, beginning with A1:

B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100)+NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)

If you wanted to start from A25, this is the form:
B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

It would if I knew the end cell, i.e. "C10", but I do not. I have no idea
which row the information will be entered. The only cell I know is the
starting cell, i.e. A3. The next cell could be A5 or A16.

Thanks

"Ron Coderre" wrote:

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

Didn't work. The formula only counts the number of non blank cells from A1
and not the blank cells between them.

A little more detail:
A1=1s2
A2=3d4
A3-A16=" "
A17=8u6
Axx=2n8
Ayy=9j6

The number of blanks betwen the first two cells is "0". Next is 13 (A2 to
A17), but what is the number of blanks between A17 and the next populated
cell in Column A where "xx" or "yy" is not known?

Thanks


"Ron Coderre" wrote:

Oops! I apologize..

B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)

That's an array formula and must be commited by holding down the [Ctrl] and
[Shift] keys when you press [Enter].

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Something is wrong with the formula. If I enter any value between A2 to A100
the answer is always "0". If I enter a value into A1 the answer is
"9999999999".

Trying to disect the equation to see if it works.

Thanks,

Dan

"Ron Coderre" wrote:

Yup...I understand, now.

See if this comes closer:

For a list of items (or blanks) in A1:A100, this formula returns the number
of contiguous non-blank cells, beginning with A1:

B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100)+NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)

If you wanted to start from A25, this is the form:
B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

It would if I knew the end cell, i.e. "C10", but I do not. I have no idea
which row the information will be entered. The only cell I know is the
starting cell, i.e. A3. The next cell could be A5 or A16.

Thanks

"Ron Coderre" wrote:

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

Dan

I didn't realize you only want to count the number of blank cells.
See if these alternatives give you something you can work with:

Note: Commit both of these array formula by holding down the [Ctrl][Shift]
keys and press [Enter].

Option 1:
B1: =MIN(NOT(ISBLANK(A2:A100))*ROW(A2:A100)+ISBLANK(A2 :A100)*10^10)-ROW(A1)-1

Copy/paste that formula from B2 down as far as you need.
The formula returns the number of blank cells between the referenced cell
and the next non-blank cell below it.

Option 2:
C1: (a cell address from Col_A: Example: A2)
B1:
=MIN(NOT(ISBLANK(OFFSET(INDIRECT(C1),1,0):A100))*R OW(OFFSET(INDIRECT(C1),1,0):A100)+ISBLANK(OFFSET(I NDIRECT(C1),1,0):A100)*10^10)-ROW(INDIRECT(C1))-1

Changing the cell address in C1 will cause the formula to return the number
of blank cells between that reference and the next non-blank cell below it.
Note: if a formula contains =" ", it is NOT blank.

Change the references to suit your situation. Right now the formulas only
look down limited number of rows (up to 100), but you can easily change that.

Any closer this time?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Didn't work. The formula only counts the number of non blank cells from A1
and not the blank cells between them.

A little more detail:
A1=1s2
A2=3d4
A3-A16=" "
A17=8u6
Axx=2n8
Ayy=9j6

The number of blanks betwen the first two cells is "0". Next is 13 (A2 to
A17), but what is the number of blanks between A17 and the next populated
cell in Column A where "xx" or "yy" is not known?

Thanks


"Ron Coderre" wrote:

Oops! I apologize..

B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)

That's an array formula and must be commited by holding down the [Ctrl] and
[Shift] keys when you press [Enter].

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Something is wrong with the formula. If I enter any value between A2 to A100
the answer is always "0". If I enter a value into A1 the answer is
"9999999999".

Trying to disect the equation to see if it works.

Thanks,

Dan

"Ron Coderre" wrote:

Yup...I understand, now.

See if this comes closer:

For a list of items (or blanks) in A1:A100, this formula returns the number
of contiguous non-blank cells, beginning with A1:

B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100)+NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)

If you wanted to start from A25, this is the form:
B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

It would if I knew the end cell, i.e. "C10", but I do not. I have no idea
which row the information will be entered. The only cell I know is the
starting cell, i.e. A3. The next cell could be A5 or A16.

Thanks

"Ron Coderre" wrote:

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

Excellent. Both work above my expectations. The second may work better for my
application since the first data point "Cell A1" is actually a lookup using
VLOOKUP to another document. This response can return the referece cell
location "Cell A1" and based off this response I can then continue the
calculations.

Your help has been greatly appreciated. I may ask for further assistance in
the near future.

Thanks

Dan

"Ron Coderre" wrote:

Dan

I didn't realize you only want to count the number of blank cells.
See if these alternatives give you something you can work with:

Note: Commit both of these array formula by holding down the [Ctrl][Shift]
keys and press [Enter].

Option 1:
B1: =MIN(NOT(ISBLANK(A2:A100))*ROW(A2:A100)+ISBLANK(A2 :A100)*10^10)-ROW(A1)-1

Copy/paste that formula from B2 down as far as you need.
The formula returns the number of blank cells between the referenced cell
and the next non-blank cell below it.

Option 2:
C1: (a cell address from Col_A: Example: A2)
B1:
=MIN(NOT(ISBLANK(OFFSET(INDIRECT(C1),1,0):A100))*R OW(OFFSET(INDIRECT(C1),1,0):A100)+ISBLANK(OFFSET(I NDIRECT(C1),1,0):A100)*10^10)-ROW(INDIRECT(C1))-1

Changing the cell address in C1 will cause the formula to return the number
of blank cells between that reference and the next non-blank cell below it.
Note: if a formula contains =" ", it is NOT blank.

Change the references to suit your situation. Right now the formulas only
look down limited number of rows (up to 100), but you can easily change that.

Any closer this time?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Didn't work. The formula only counts the number of non blank cells from A1
and not the blank cells between them.

A little more detail:
A1=1s2
A2=3d4
A3-A16=" "
A17=8u6
Axx=2n8
Ayy=9j6

The number of blanks betwen the first two cells is "0". Next is 13 (A2 to
A17), but what is the number of blanks between A17 and the next populated
cell in Column A where "xx" or "yy" is not known?

Thanks


"Ron Coderre" wrote:

Oops! I apologize..

B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)

That's an array formula and must be commited by holding down the [Ctrl] and
[Shift] keys when you press [Enter].

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Something is wrong with the formula. If I enter any value between A2 to A100
the answer is always "0". If I enter a value into A1 the answer is
"9999999999".

Trying to disect the equation to see if it works.

Thanks,

Dan

"Ron Coderre" wrote:

Yup...I understand, now.

See if this comes closer:

For a list of items (or blanks) in A1:A100, this formula returns the number
of contiguous non-blank cells, beginning with A1:

B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100)+NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)

If you wanted to start from A25, this is the form:
B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

It would if I knew the end cell, i.e. "C10", but I do not. I have no idea
which row the information will be entered. The only cell I know is the
starting cell, i.e. A3. The next cell could be A5 or A16.

Thanks

"Ron Coderre" wrote:

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan