Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default Formula to return position of the next Non-blank cell in a column

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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 524
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return next non-Blank Cell in a Column ? Jakobshavn Isbrae Excel Worksheet Functions 3 May 23rd 07 10:28 PM
Return Numeric Value to their Matched Value Position in Single Column Sam via OfficeKB.com Excel Worksheet Functions 3 March 6th 07 06:18 PM
Formula to return cell position AJPendragon Excel Worksheet Functions 1 February 6th 06 10:34 PM
Excel formula to find position of the contents of a cell within a column. [email protected] Excel Discussion (Misc queries) 3 September 26th 05 03:52 PM
find the first blank cell in a range and return me it's position steve alcock Links and Linking in Excel 2 May 13th 05 09:03 AM


All times are GMT +1. The time now is 06:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"