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 quot;Name Boxquot; 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-Proquot;Danquot; wrote:
gt; Is there a function that will return the number of rows between two cells of
gt; unknown distance, similar to selecting the first cell and pressing
gt; SHIFT CRTL DOWN KEY and the number of rows is displayed in the quot;Name Boxquot; in
gt; the upper left corner of the spread sheet?
gt;
gt; Thanks
gt;
gt; Dan
It would if I knew the end cell, i.e. quot;C10quot;, 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
quot;Ron Coderrequot; wrote:
gt; Try something like this:
gt;
gt; =ROWS(A1:C10)
gt;
gt; In that case, the function returns 10.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Danquot; wrote:
gt;
gt; gt; Is there a function that will return the number of rows between two cells of
gt; gt; unknown distance, similar to selecting the first cell and pressing
gt; gt; SHIFT CRTL DOWN KEY and the number of rows is displayed in the quot;Name Boxquot; in
gt; gt; the upper left corner of the spread sheet?
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; 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-Proquot;Danquot; wrote:
gt; It would if I knew the end cell, i.e. quot;C10quot;, but I do not. I have no idea
gt; which row the information will be entered. The only cell I know is the
gt; starting cell, i.e. A3. The next cell could be A5 or A16.
gt;
gt; Thanks
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt;
gt; gt; =ROWS(A1:C10)
gt; gt;
gt; gt; In that case, the function returns 10.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Danquot; wrote:
gt; gt;
gt; gt; gt; Is there a function that will return the number of rows between two cells of
gt; gt; gt; unknown distance, similar to selecting the first cell and pressing
gt; gt; gt; SHIFT CRTL DOWN KEY and the number of rows is displayed in the quot;Name Boxquot; in
gt; gt; gt; the upper left corner of the spread sheet?
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; Dan
Something is wrong with the formula. If I enter any value between A2 to A100
the answer is always quot;0quot;. If I enter a value into A1 the answer is
quot;9999999999quot;.
Trying to disect the equation to see if it works.
Thanks,
Dan
quot;Ron Coderrequot; wrote:
gt; Yup...I understand, now.
gt;
gt; See if this comes closer:
gt;
gt; For a list of items (or blanks) in A1:A100, this formula returns the number
gt; of contiguous non-blank cells, beginning with A1:
gt;
gt; B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100) NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)
gt;
gt; If you wanted to start from A25, this is the form:
gt; B1:
gt; =MIN(ISBLANK(A25:A100)*ROW(A25:A100) NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)
gt;
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Danquot; wrote:
gt;
gt; gt; It would if I knew the end cell, i.e. quot;C10quot;, but I do not. I have no idea
gt; gt; which row the information will be entered. The only cell I know is the
gt; gt; starting cell, i.e. A3. The next cell could be A5 or A16.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try something like this:
gt; gt; gt;
gt; gt; gt; =ROWS(A1:C10)
gt; gt; gt;
gt; gt; gt; In that case, the function returns 10.
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Danquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Is there a function that will return the number of rows between two cells of
gt; gt; gt; gt; unknown distance, similar to selecting the first cell and pressing
gt; gt; gt; gt; SHIFT CRTL DOWN KEY and the number of rows is displayed in the quot;Name Boxquot; in
gt; gt; gt; gt; the upper left corner of the spread sheet?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt;
gt; gt; gt; gt; 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-Proquot;Danquot; wrote:
gt; Something is wrong with the formula. If I enter any value between A2 to A100
gt; the answer is always quot;0quot;. If I enter a value into A1 the answer is
gt; quot;9999999999quot;.
gt;
gt; Trying to disect the equation to see if it works.
gt;
gt; Thanks,
gt;
gt; Dan
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Yup...I understand, now.
gt; gt;
gt; gt; See if this comes closer:
gt; gt;
gt; gt; For a list of items (or blanks) in A1:A100, this formula returns the number
gt; gt; of contiguous non-blank cells, beginning with A1:
gt; gt;
gt; gt; B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100) NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)
gt; gt;
gt; gt; If you wanted to start from A25, this is the form:
gt; gt; B1:
gt; gt; =MIN(ISBLANK(A25:A100)*ROW(A25:A100) NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)
gt; gt;
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Danquot; wrote:
gt; gt;
gt; gt; gt; It would if I knew the end cell, i.e. quot;C10quot;, but I do not. I have no idea
gt; gt; gt; which row the information will be entered. The only cell I know is the
gt; gt; gt; starting cell, i.e. A3. The next cell could be A5 or A16.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Try something like this:
gt; gt; gt; gt;
gt; gt; gt; gt; =ROWS(A1:C10)
gt; gt; gt; gt;
gt; gt; gt; gt; In that case, the function returns 10.
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Danquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Is there a function that will return the number of rows between two cells of
gt; gt; gt; gt; gt; unknown distance, similar to selecting the first cell and pressing
gt; gt; gt; gt; gt; SHIFT CRTL DOWN KEY and the number of rows is displayed in the quot;Name Boxquot; in
gt; gt; gt; gt; gt; the upper left corner of the spread sheet?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; 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=quot; quot;
A17=8u6
Axx=2n8
Ayy=9j6
The number of blanks betwen the first two cells is quot;0quot;. Next is 13 (A2 to
A17), but what is the number of blanks between A17 and the next populated
cell in Column A where quot;xxquot; or quot;yyquot; is not known?
Thanksquot;Ron Coderrequot; wrote:
gt; Oops! I apologize..
gt;
gt; B1:
gt; =MIN(ISBLANK(A25:A100)*ROW(A25:A100) NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)
gt;
gt; That's an array formula and must be commited by holding down the [Ctrl] and
gt; [Shift] keys when you press [Enter].
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Danquot; wrote:
gt;
gt; gt; Something is wrong with the formula. If I enter any value between A2 to A100
gt; gt; the answer is always quot;0quot;. If I enter a value into A1 the answer is
gt; gt; quot;9999999999quot;.
gt; gt;
gt; gt; Trying to disect the equation to see if it works.
gt; gt;
gt; gt; Thanks,
gt; gt;
gt; gt; Dan
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Yup...I understand, now.
gt; gt; gt;
gt; gt; gt; See if this comes closer:
gt; gt; gt;
gt; gt; gt; For a list of items (or blanks) in A1:A100, this formula returns the number
gt; gt; gt; of contiguous non-blank cells, beginning with A1:
gt; gt; gt;
gt; gt; gt; B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100) NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)
gt; gt; gt;
gt; gt; gt; If you wanted to start from A25, this is the form:
gt; gt; gt; B1:
gt; gt; gt; =MIN(ISBLANK(A25:A100)*ROW(A25:A100) NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Danquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; It would if I knew the end cell, i.e. quot;C10quot;, but I do not. I have no idea
gt; gt; gt; gt; which row the information will be entered. The only cell I know is the
gt; gt; gt; gt; starting cell, i.e. A3. The next cell could be A5 or A16.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Try something like this:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =ROWS(A1:C10)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; In that case, the function returns 10.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; ***********
gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; Ron
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Danquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Is there a function that will return the number of rows between two cells of
gt; gt; gt; gt; gt; gt; unknown distance, similar to selecting the first cell and pressing
gt; gt; gt; gt; gt; gt; SHIFT CRTL DOWN KEY and the number of rows is displayed in the quot;Name Boxquot; in
gt; gt; gt; gt; gt; gt; the upper left corner of the spread sheet?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; 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 =quot; quot;, 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-Proquot;Danquot; wrote:
gt; Didn't work. The formula only counts the number of non blank cells from A1
gt; and not the blank cells between them.
gt;
gt; A little more detail:
gt; A1=1s2
gt; A2=3d4
gt; A3-A16=quot; quot;
gt; A17=8u6
gt; Axx=2n8
gt; Ayy=9j6
gt;
gt; The number of blanks betwen the first two cells is quot;0quot;. Next is 13 (A2 to
gt; A17), but what is the number of blanks between A17 and the next populated
gt; cell in Column A where quot;xxquot; or quot;yyquot; is not known?
gt;
gt; Thanks
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Oops! I apologize..
gt; gt;
gt; gt; B1:
gt; gt; =MIN(ISBLANK(A25:A100)*ROW(A25:A100) NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)
gt; gt;
gt; gt; That's an array formula and must be commited by holding down the [Ctrl] and
gt; gt; [Shift] keys when you press [Enter].
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Danquot; wrote:
gt; gt;
gt; gt; gt; Something is wrong with the formula. If I enter any value between A2 to A100
gt; gt; gt; the answer is always quot;0quot;. If I enter a value into A1 the answer is
gt; gt; gt; quot;9999999999quot;.
gt; gt; gt;
gt; gt; gt; Trying to disect the equation to see if it works.
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt;
gt; gt; gt; Dan
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Yup...I understand, now.
gt; gt; gt; gt;
gt; gt; gt; gt; See if this comes closer:
gt; gt; gt; gt;
gt; gt; gt; gt; For a list of items (or blanks) in A1:A100, this formula returns the number
gt; gt; gt; gt; of contiguous non-blank cells, beginning with A1:
gt; gt; gt; gt;
gt; gt; gt; gt; B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100) NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)
gt; gt; gt; gt;
gt; gt; gt; gt; If you wanted to start from A25, this is the form:
gt; gt; gt; gt; B1:
gt; gt; gt; gt; =MIN(ISBLANK(A25:A100)*ROW(A25:A100) NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Danquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; It would if I knew the end cell, i.e. quot;C10quot;, but I do not. I have no idea
gt; gt; gt; gt; gt; which row the information will be entered. The only cell I know is the
gt; gt; gt; gt; gt; starting cell, i.e. A3. The next cell could be A5 or A16.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Try something like this:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =ROWS(A1:C10)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; In that case, the function returns 10.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; ***********
gt; gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; gt; Ron
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Danquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Is there a function that will return the number of rows between two cells of
gt; gt; gt; gt; gt; gt; gt; unknown distance, similar to selecting the first cell and pressing
gt; gt; gt; gt; gt; gt; gt; SHIFT CRTL DOWN KEY and the number of rows is displayed in the quot;Name Boxquot; in
gt; gt; gt; gt; gt; gt; gt; the upper left corner of the spread sheet?
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Dan
Excellent. Both work above my expectations. The second may work better for my
application since the first data point quot;Cell A1quot; is actually a lookup using
VLOOKUP to another document. This response can return the referece cell
location quot;Cell A1quot; 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
quot;Ron Coderrequot; wrote:
gt; Dan
gt;
gt; I didn't realize you only want to count the number of blank cells.
gt; See if these alternatives give you something you can work with:
gt;
gt; Note: Commit both of these array formula by holding down the [Ctrl][Shift]
gt; keys and press [Enter].
gt;
gt; Option 1:
gt; B1: =MIN(NOT(ISBLANK(A2:A100))*ROW(A2:A100) ISBLANK(A2 :A100)*10^10)-ROW(A1)-1
gt;
gt; Copy/paste that formula from B2 down as far as you need.
gt; The formula returns the number of blank cells between the referenced cell
gt; and the next non-blank cell below it.
gt;
gt; Option 2:
gt; C1: (a cell address from Col_A: Example: A2)
gt; B1:
gt; =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
gt;
gt; Changing the cell address in C1 will cause the formula to return the number
gt; of blank cells between that reference and the next non-blank cell below it.
gt; Note: if a formula contains =quot; quot;, it is NOT blank.
gt;
gt; Change the references to suit your situation. Right now the formulas only
gt; look down limited number of rows (up to 100), but you can easily change that.
gt;
gt; Any closer this time?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Danquot; wrote:
gt;
gt; gt; Didn't work. The formula only counts the number of non blank cells from A1
gt; gt; and not the blank cells between them.
gt; gt;
gt; gt; A little more detail:
gt; gt; A1=1s2
gt; gt; A2=3d4
gt; gt; A3-A16=quot; quot;
gt; gt; A17=8u6
gt; gt; Axx=2n8
gt; gt; Ayy=9j6
gt; gt;
gt; gt; The number of blanks betwen the first two cells is quot;0quot;. Next is 13 (A2 to
gt; gt; A17), but what is the number of blanks between A17 and the next populated
gt; gt; cell in Column A where quot;xxquot; or quot;yyquot; is not known?
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Oops! I apologize..
gt; gt; gt;
gt; gt; gt; B1:
gt; gt; gt; =MIN(ISBLANK(A25:A100)*ROW(A25:A100) NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)
gt; gt; gt;
gt; gt; gt; That's an array formula and must be commited by holding down the [Ctrl] and
gt; gt; gt; [Shift] keys when you press [Enter].
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Danquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Something is wrong with the formula. If I enter any value between A2 to A100
gt; gt; gt; gt; the answer is always quot;0quot;. If I enter a value into A1 the answer is
gt; gt; gt; gt; quot;9999999999quot;.
gt; gt; gt; gt;
gt; gt; gt; gt; Trying to disect the equation to see if it works.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks,
gt; gt; gt; gt;
gt; gt; gt; gt; Dan
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Yup...I understand, now.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; See if this comes closer:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; For a list of items (or blanks) in A1:A100, this formula returns the number
gt; gt; gt; gt; gt; of contiguous non-blank cells, beginning with A1:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100) NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; If you wanted to start from A25, this is the form:
gt; gt; gt; gt; gt; B1:
gt; gt; gt; gt; gt; =MIN(ISBLANK(A25:A100)*ROW(A25:A100) NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; ***********
gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; Ron
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Danquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; It would if I knew the end cell, i.e. quot;C10quot;, but I do not. I have no idea
gt; gt; gt; gt; gt; gt; which row the information will be entered. The only cell I know is the
gt; gt; gt; gt; gt; gt; starting cell, i.e. A3. The next cell could be A5 or A16.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Try something like this:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; =ROWS(A1:C10)
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; In that case, the function returns 10.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; ***********
gt; gt; gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; gt; gt; Ron
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; quot;Danquot; wrote:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; Is there a function that will return the number of rows between two cells of
gt; gt; gt; gt; gt; gt; gt; gt; unknown distance, similar to selecting the first cell and pressing
gt; gt; gt; gt; gt; gt; gt; gt; SHIFT CRTL DOWN KEY and the number of rows is displayed in the quot;Name Boxquot; in
gt; gt; gt; gt; gt; gt; gt; gt; the upper left corner of the spread sheet?
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; Dan
留言列表
