Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a column
Given the position of a non-blank cell in a column, is there a way to return
the position of the next non-blank cell in the same column? Example: The given position of A1 is not blank. There is an unknown number of consecutive spaces (blank cells) that follow in the same column. Is there a formula that will tell me the position of the next non-blank cell? A 1 Test 2 3 4 5 6 UnknownData The formula should be able to somehow return the row number or position of the next non-blank cell, in this case would be row 6 or cell A6. Keep in mind that the next value is unknown. Is this possible? Thanks, Paul |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a column
How about a macro?
Sub nextnonblankvalue() MsgBox ActiveCell.End(xlDown) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "PCLIVE" wrote in message ... Given the position of a non-blank cell in a column, is there a way to return the position of the next non-blank cell in the same column? Example: The given position of A1 is not blank. There is an unknown number of consecutive spaces (blank cells) that follow in the same column. Is there a formula that will tell me the position of the next non-blank cell? A 1 Test 2 3 4 5 6 UnknownData The formula should be able to somehow return the row number or position of the next non-blank cell, in this case would be row 6 or cell A6. Keep in mind that the next value is unknown. Is this possible? Thanks, Paul |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a column
Put this in a REGULAR vba module. On the worksheet =nr(a1) or =nr(g3) etc
Function nr(x As Range) Application.Volatile nr = x.End(xlDown) End Function -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... How about a macro? Sub nextnonblankvalue() MsgBox ActiveCell.End(xlDown) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "PCLIVE" wrote in message ... Given the position of a non-blank cell in a column, is there a way to return the position of the next non-blank cell in the same column? Example: The given position of A1 is not blank. There is an unknown number of consecutive spaces (blank cells) that follow in the same column. Is there a formula that will tell me the position of the next non-blank cell? A 1 Test 2 3 4 5 6 UnknownData The formula should be able to somehow return the row number or position of the next non-blank cell, in this case would be row 6 or cell A6. Keep in mind that the next value is unknown. Is this possible? Thanks, Paul |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a column
That's pretty cool Don. I think I can make this work.
Thanks a bunch. Paul "Don Guillett" wrote in message ... Put this in a REGULAR vba module. On the worksheet =nr(a1) or =nr(g3) etc Function nr(x As Range) Application.Volatile nr = x.End(xlDown) End Function -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... How about a macro? Sub nextnonblankvalue() MsgBox ActiveCell.End(xlDown) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "PCLIVE" wrote in message ... Given the position of a non-blank cell in a column, is there a way to return the position of the next non-blank cell in the same column? Example: The given position of A1 is not blank. There is an unknown number of consecutive spaces (blank cells) that follow in the same column. Is there a formula that will tell me the position of the next non-blank cell? A 1 Test 2 3 4 5 6 UnknownData The formula should be able to somehow return the row number or position of the next non-blank cell, in this case would be row 6 or cell A6. Keep in mind that the next value is unknown. Is this possible? Thanks, Paul |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a col
try:
=INDEX(INDIRECT(C1 &":A1000"),MATCH(TRUE,INDIRECT(C1 &":A1000")<"",0),0) Enter with Ctrl+Shift+Enter C1 contains address of the first blank cell AFTER your reference cell e.g A2 in your example "PCLIVE" wrote: I had already thought of that, which is pretty easy. But I wanted to try and achieve this with a formula since I have no other reasons to run code here. Any other ideas? "Don Guillett" wrote in message ... How about a macro? Sub nextnonblankvalue() MsgBox ActiveCell.End(xlDown) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "PCLIVE" wrote in message ... Given the position of a non-blank cell in a column, is there a way to return the position of the next non-blank cell in the same column? Example: The given position of A1 is not blank. There is an unknown number of consecutive spaces (blank cells) that follow in the same column. Is there a formula that will tell me the position of the next non-blank cell? A 1 Test 2 3 4 5 6 UnknownData The formula should be able to somehow return the row number or position of the next non-blank cell, in this case would be row 6 or cell A6. Keep in mind that the next value is unknown. Is this possible? Thanks, Paul |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a col
... row number ..
=MATCH(INDEX(INDIRECT(C1 &":A100"),MATCH(TRUE,INDIRECT(C1 &":A100")<"",0),0),A:A,0) with CSE "PCLIVE" wrote: I had already thought of that, which is pretty easy. But I wanted to try and achieve this with a formula since I have no other reasons to run code here. Any other ideas? "Don Guillett" wrote in message ... How about a macro? Sub nextnonblankvalue() MsgBox ActiveCell.End(xlDown) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "PCLIVE" wrote in message ... Given the position of a non-blank cell in a column, is there a way to return the position of the next non-blank cell in the same column? Example: The given position of A1 is not blank. There is an unknown number of consecutive spaces (blank cells) that follow in the same column. Is there a formula that will tell me the position of the next non-blank cell? A 1 Test 2 3 4 5 6 UnknownData The formula should be able to somehow return the row number or position of the next non-blank cell, in this case would be row 6 or cell A6. Keep in mind that the next value is unknown. Is this possible? Thanks, Paul |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a column
If the position (address?) of the first non-blank is known then all you need
to do is start looking for the first non-blank after the known. Based on your sample: ="A"&INDEX(ROW(A2:A20),MATCH("*",A2:A20,0)) Result = A6 Or is there more to it than that? -- Biff Microsoft Excel MVP "PCLIVE" wrote in message ... Given the position of a non-blank cell in a column, is there a way to return the position of the next non-blank cell in the same column? Example: The given position of A1 is not blank. There is an unknown number of consecutive spaces (blank cells) that follow in the same column. Is there a formula that will tell me the position of the next non-blank cell? A 1 Test 2 3 4 5 6 UnknownData The formula should be able to somehow return the row number or position of the next non-blank cell, in this case would be row 6 or cell A6. Keep in mind that the next value is unknown. Is this possible? Thanks, Paul |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a column
Do you mean that wherever the first occurrence is you want the next
occurrence? If so =INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100,MATCH(TRUE ,A1:A100<"",0)+1):INDEX(A1:A100,100)<"",0)+MATCH (TRUE,A1:A100<"",0)) array entered will give you the contents of the next non blank cell in A1:A100, if you want the cell address =CELL("address",INDEX(A1:A100,MATCH(TRUE,INDEX(A1: A100,MATCH(TRUE,A1:A100<"",0)+1):INDEX(A1:A100,10 0)<"",0)+MATCH(TRUE,A1:A100<"",0))) the position =MATCH(TRUE,INDEX(A1:A100,MATCH(TRUE,A1:A100<"",0 )+1):INDEX(A1:A100,100)<"",0)+MATCH(TRUE,A1:A100< "",0) these are all non volatile Of course you might want to add a test to make sure it doesn't return an error if there is 1 or less occurrences in the range like =IF(COUNTA(A1:A100<=1,"",formula -- Regards, Peo Sjoblom "PCLIVE" wrote in message ... Given the position of a non-blank cell in a column, is there a way to return the position of the next non-blank cell in the same column? Example: The given position of A1 is not blank. There is an unknown number of consecutive spaces (blank cells) that follow in the same column. Is there a formula that will tell me the position of the next non-blank cell? A 1 Test 2 3 4 5 6 UnknownData The formula should be able to somehow return the row number or position of the next non-blank cell, in this case would be row 6 or cell A6. Keep in mind that the next value is unknown. Is this possible? Thanks, Paul |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a column
That's pretty interesting too. I will play around with this.
Thanks, Paul "T. Valko" wrote in message ... If the position (address?) of the first non-blank is known then all you need to do is start looking for the first non-blank after the known. Based on your sample: ="A"&INDEX(ROW(A2:A20),MATCH("*",A2:A20,0)) Result = A6 Or is there more to it than that? -- Biff Microsoft Excel MVP "PCLIVE" wrote in message ... Given the position of a non-blank cell in a column, is there a way to return the position of the next non-blank cell in the same column? Example: The given position of A1 is not blank. There is an unknown number of consecutive spaces (blank cells) that follow in the same column. Is there a formula that will tell me the position of the next non-blank cell? A 1 Test 2 3 4 5 6 UnknownData The formula should be able to somehow return the row number or position of the next non-blank cell, in this case would be row 6 or cell A6. Keep in mind that the next value is unknown. Is this possible? Thanks, Paul |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a column
Note: that will only work on TEXT (as your sample shows). If the next
non-blank might be either text or numeric it's not much different: ="A"&INDEX(ROW(A2:A20),MATCH(TRUE,INDEX(A2:A20<"" ,,1),0)) -- Biff Microsoft Excel MVP "PCLIVE" wrote in message ... That's pretty interesting too. I will play around with this. Thanks, Paul "T. Valko" wrote in message ... If the position (address?) of the first non-blank is known then all you need to do is start looking for the first non-blank after the known. Based on your sample: ="A"&INDEX(ROW(A2:A20),MATCH("*",A2:A20,0)) Result = A6 Or is there more to it than that? -- Biff Microsoft Excel MVP "PCLIVE" wrote in message ... Given the position of a non-blank cell in a column, is there a way to return the position of the next non-blank cell in the same column? Example: The given position of A1 is not blank. There is an unknown number of consecutive spaces (blank cells) that follow in the same column. Is there a formula that will tell me the position of the next non-blank cell? A 1 Test 2 3 4 5 6 UnknownData The formula should be able to somehow return the row number or position of the next non-blank cell, in this case would be row 6 or cell A6. Keep in mind that the next value is unknown. Is this possible? Thanks, Paul |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a column
these are all non volatile
CELL( ) is volatile -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... Do you mean that wherever the first occurrence is you want the next occurrence? If so =INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100,MATCH(TRUE ,A1:A100<"",0)+1):INDEX(A1:A100,100)<"",0)+MATCH (TRUE,A1:A100<"",0)) array entered will give you the contents of the next non blank cell in A1:A100, if you want the cell address =CELL("address",INDEX(A1:A100,MATCH(TRUE,INDEX(A1: A100,MATCH(TRUE,A1:A100<"",0)+1):INDEX(A1:A100,10 0)<"",0)+MATCH(TRUE,A1:A100<"",0))) the position =MATCH(TRUE,INDEX(A1:A100,MATCH(TRUE,A1:A100<"",0 )+1):INDEX(A1:A100,100)<"",0)+MATCH(TRUE,A1:A100< "",0) these are all non volatile Of course you might want to add a test to make sure it doesn't return an error if there is 1 or less occurrences in the range like =IF(COUNTA(A1:A100<=1,"",formula -- Regards, Peo Sjoblom "PCLIVE" wrote in message ... Given the position of a non-blank cell in a column, is there a way to return the position of the next non-blank cell in the same column? Example: The given position of A1 is not blank. There is an unknown number of consecutive spaces (blank cells) that follow in the same column. Is there a formula that will tell me the position of the next non-blank cell? A 1 Test 2 3 4 5 6 UnknownData The formula should be able to somehow return the row number or position of the next non-blank cell, in this case would be row 6 or cell A6. Keep in mind that the next value is unknown. Is this possible? Thanks, Paul |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a column
Yes you are correct, using address instead would fix that
-- Regards, Peo Sjoblom "T. Valko" wrote in message ... these are all non volatile CELL( ) is volatile -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... Do you mean that wherever the first occurrence is you want the next occurrence? If so =INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100,MATCH(TRUE ,A1:A100<"",0)+1):INDEX(A1:A100,100)<"",0)+MATCH (TRUE,A1:A100<"",0)) array entered will give you the contents of the next non blank cell in A1:A100, if you want the cell address =CELL("address",INDEX(A1:A100,MATCH(TRUE,INDEX(A1: A100,MATCH(TRUE,A1:A100<"",0)+1):INDEX(A1:A100,10 0)<"",0)+MATCH(TRUE,A1:A100<"",0))) the position =MATCH(TRUE,INDEX(A1:A100,MATCH(TRUE,A1:A100<"",0 )+1):INDEX(A1:A100,100)<"",0)+MATCH(TRUE,A1:A100< "",0) these are all non volatile Of course you might want to add a test to make sure it doesn't return an error if there is 1 or less occurrences in the range like =IF(COUNTA(A1:A100<=1,"",formula -- Regards, Peo Sjoblom "PCLIVE" wrote in message ... Given the position of a non-blank cell in a column, is there a way to return the position of the next non-blank cell in the same column? Example: The given position of A1 is not blank. There is an unknown number of consecutive spaces (blank cells) that follow in the same column. Is there a formula that will tell me the position of the next non-blank cell? A 1 Test 2 3 4 5 6 UnknownData The formula should be able to somehow return the row number or position of the next non-blank cell, in this case would be row 6 or cell A6. Keep in mind that the next value is unknown. Is this possible? Thanks, Paul |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a column
Mon, 16 Jul 2007 11:07:40 -0500 from Don Guillett <dguillett1
@austin.rr.com: How about a macro? Sub nextnonblankvalue() MsgBox ActiveCell.End(xlDown) End Sub Suppose A1 through D1 are non-empty, and E1 is empty. If you're in A1, won't the above macro return D1 instead of the wanted B1? -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to return position of the next Non-blank cell in a column
Where did B1 come from?
This will look DOWN from the activecell. If in cell b1 it will look ONLY in column B, A looks in A, etc. -- Don Guillett Microsoft MVP Excel SalesAid Software "Stan Brown" wrote in message t... Mon, 16 Jul 2007 11:07:40 -0500 from Don Guillett <dguillett1 @austin.rr.com: How about a macro? Sub nextnonblankvalue() MsgBox ActiveCell.End(xlDown) End Sub Suppose A1 through D1 are non-empty, and E1 is empty. If you're in A1, won't the above macro return D1 instead of the wanted B1? -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return next non-Blank Cell in a Column ? | Excel Worksheet Functions | |||
Return Numeric Value to their Matched Value Position in Single Column | Excel Worksheet Functions | |||
Formula to return cell position | Excel Worksheet Functions | |||
Excel formula to find position of the contents of a cell within a column. | Excel Discussion (Misc queries) | |||
find the first blank cell in a range and return me it's position | Links and Linking in Excel |