|
Function similar to SHIFT+CTRL+DOWN KEY
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 |
Function similar to SHIFT+CTRL+DOWN KEY
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 |
Function similar to SHIFT+CTRL+DOWN KEY
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 |
Function similar to SHIFT+CTRL+DOWN KEY
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 |
Function similar to SHIFT+CTRL+DOWN KEY
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 |
Function similar to SHIFT+CTRL+DOWN KEY
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 |
Function similar to SHIFT+CTRL+DOWN KEY
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 |
Function similar to SHIFT+CTRL+DOWN KEY
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 |
Function similar to SHIFT+CTRL+DOWN KEY
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 |
| All times are GMT +1. The time now is 12:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com