Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 8th 07, 05:57 PM posted to microsoft.public.excel.worksheet.functions
DW DW is offline
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 26
Default How to: Find first empty cell in column

I believe there is a formula that has something about max(row, etc), but
cannot recall it.

Col A would contain a list of numbers.
I'm trying to find the first empty (blank) cell in col A.
Even tho I could write a macro to do this, I'm needing the cell formula.

Your help is greatly appreciated!!

Don



  #2   Report Post  
Old October 8th 07, 06:05 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 3,268
Default How to: Find first empty cell in column

=MATCH(TRUE,A1:A10="",0)

entered with ctrl + shift & enter will find the first and return the index
number so using A1:A10
and the first empty is in A4 it will return 4


--


Regards,


Peo Sjoblom


"DW" wrote in message
...
I believe there is a formula that has something about max(row, etc), but
cannot recall it.

Col A would contain a list of numbers.
I'm trying to find the first empty (blank) cell in col A.
Even tho I could write a macro to do this, I'm needing the cell formula.

Your help is greatly appreciated!!

Don



  #3   Report Post  
Old October 8th 07, 06:20 PM posted to microsoft.public.excel.worksheet.functions
DW DW is offline
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 26
Default How to: Find first empty cell in column

Thanks Peo, but, (and I probably didn't make myself clear, sorry),

let's say, A4 is first empty, how would I get the cell value of A3?

Hope this clears it up. Again, sorry.

Don

"Peo Sjoblom" wrote in message
...
=MATCH(TRUE,A1:A10="",0)

entered with ctrl + shift & enter will find the first and return the index
number so using A1:A10
and the first empty is in A4 it will return 4


--


Regards,


Peo Sjoblom


"DW" wrote in message
...
I believe there is a formula that has something about max(row, etc), but
cannot recall it.

Col A would contain a list of numbers.
I'm trying to find the first empty (blank) cell in col A.
Even tho I could write a macro to do this, I'm needing the cell formula.

Your help is greatly appreciated!!

Don





  #4   Report Post  
Old October 8th 07, 06:49 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 791
Default How to: Find first empty cell in column

=INDEX(A1:A10,MATCH(TRUE,A1:A10="",1)-1,0)
Don't forget to use the ctrl-shift enter combination to enter this formula
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"DW" wrote:

Thanks Peo, but, (and I probably didn't make myself clear, sorry),

let's say, A4 is first empty, how would I get the cell value of A3?

Hope this clears it up. Again, sorry.

Don

"Peo Sjoblom" wrote in message
...
=MATCH(TRUE,A1:A10="",0)

entered with ctrl + shift & enter will find the first and return the index
number so using A1:A10
and the first empty is in A4 it will return 4


--


Regards,


Peo Sjoblom


"DW" wrote in message
...
I believe there is a formula that has something about max(row, etc), but
cannot recall it.

Col A would contain a list of numbers.
I'm trying to find the first empty (blank) cell in col A.
Even tho I could write a macro to do this, I'm needing the cell formula.

Your help is greatly appreciated!!

Don






  #5   Report Post  
Old October 8th 07, 06:52 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 791
Default How to: Find first empty cell in column

My bad, =INDEX(A1:A10,MATCH(TRUE,A1:A10="",0)-1,0)
Note that in the match formula the last parameter must be zero.

Sorry about that


--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Michael" wrote:

=INDEX(A1:A10,MATCH(TRUE,A1:A10="",1)-1,0)
Don't forget to use the ctrl-shift enter combination to enter this formula
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"DW" wrote:

Thanks Peo, but, (and I probably didn't make myself clear, sorry),

let's say, A4 is first empty, how would I get the cell value of A3?

Hope this clears it up. Again, sorry.

Don

"Peo Sjoblom" wrote in message
...
=MATCH(TRUE,A1:A10="",0)

entered with ctrl + shift & enter will find the first and return the index
number so using A1:A10
and the first empty is in A4 it will return 4


--


Regards,


Peo Sjoblom


"DW" wrote in message
...
I believe there is a formula that has something about max(row, etc), but
cannot recall it.

Col A would contain a list of numbers.
I'm trying to find the first empty (blank) cell in col A.
Even tho I could write a macro to do this, I'm needing the cell formula.

Your help is greatly appreciated!!

Don








  #6   Report Post  
Old October 8th 07, 06:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 638
Default How to: Find first empty cell in column

Ctrl+Shift+Enter
=INDEX(A1:A10,MATCH(TRUE,A1:A10="",0)-1,0)
DW wrote:
Thanks Peo, but, (and I probably didn't make myself clear, sorry),

let's say, A4 is first empty, how would I get the cell value of A3?

Hope this clears it up. Again, sorry.

Don

"Peo Sjoblom" wrote in message
...
=MATCH(TRUE,A1:A10="",0)

entered with ctrl + shift & enter will find the first and return the index
number so using A1:A10
and the first empty is in A4 it will return 4


--


Regards,


Peo Sjoblom


"DW" wrote in message
...
I believe there is a formula that has something about max(row, etc), but
cannot recall it.

Col A would contain a list of numbers.
I'm trying to find the first empty (blank) cell in col A.
Even tho I could write a macro to do this, I'm needing the cell formula.

Your help is greatly appreciated!!

Don




  #7   Report Post  
Old October 8th 07, 07:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default How to: Find first empty cell in column

And you don't need to use CSE to commit it.

Pete

On Oct 8, 6:52 pm, Michael wrote:
My bad, =INDEX(A1:A10,MATCH(TRUE,A1:A10="",0)-1,0)
Note that in the match formula the last parameter must be zero.

Sorry about that

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.



"Michael" wrote:
=INDEX(A1:A10,MATCH(TRUE,A1:A10="",1)-1,0)
Don't forget to use the ctrl-shift enter combination to enter this formula
--
If this posting was helpful, please click on the Yes button.
Regards,


Michael Arch.


"DW" wrote:


Thanks Peo, but, (and I probably didn't make myself clear, sorry),


let's say, A4 is first empty, how would I get the cell value of A3?


Hope this clears it up. Again, sorry.


Don


"Peo Sjoblom" wrote in message
...
=MATCH(TRUE,A1:A10="",0)


entered with ctrl + shift & enter will find the first and return the index
number so using A1:A10
and the first empty is in A4 it will return 4


--


Regards,


Peo Sjoblom


"DW" wrote in message
...
I believe there is a formula that has something about max(row, etc), but
cannot recall it.


Col A would contain a list of numbers.
I'm trying to find the first empty (blank) cell in col A.
Even tho I could write a macro to do this, I'm needing the cell formula.


Your help is greatly appreciated!!


Don- Hide quoted text -


- Show quoted text -



  #8   Report Post  
Old October 8th 07, 07:25 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 3,268
Default How to: Find first empty cell in column

You do need to enter it with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"Pete_UK" wrote in message
ups.com...
And you don't need to use CSE to commit it.

Pete

On Oct 8, 6:52 pm, Michael wrote:
My bad, =INDEX(A1:A10,MATCH(TRUE,A1:A10="",0)-1,0)
Note that in the match formula the last parameter must be zero.

Sorry about that

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.



"Michael" wrote:
=INDEX(A1:A10,MATCH(TRUE,A1:A10="",1)-1,0)
Don't forget to use the ctrl-shift enter combination to enter this
formula
--
If this posting was helpful, please click on the Yes button.
Regards,


Michael Arch.


"DW" wrote:


Thanks Peo, but, (and I probably didn't make myself clear, sorry),


let's say, A4 is first empty, how would I get the cell value of A3?


Hope this clears it up. Again, sorry.


Don


"Peo Sjoblom" wrote in message
...
=MATCH(TRUE,A1:A10="",0)


entered with ctrl + shift & enter will find the first and return
the index
number so using A1:A10
and the first empty is in A4 it will return 4


--


Regards,


Peo Sjoblom


"DW" wrote in message
...
I believe there is a formula that has something about max(row,
etc), but
cannot recall it.


Col A would contain a list of numbers.
I'm trying to find the first empty (blank) cell in col A.
Even tho I could write a macro to do this, I'm needing the cell
formula.


Your help is greatly appreciated!!


Don- Hide quoted text -


- Show quoted text -





  #9   Report Post  
Old October 8th 07, 07:59 PM posted to microsoft.public.excel.worksheet.functions
DW DW is offline
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 26
Default How to: Find first empty cell in column

Thanks everyone....

Now, this might be a dumb question, but why the ctrl+shift + enter combo to
commit the formula?


Don


  #10   Report Post  
Old October 8th 07, 08:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 3,268
Default How to: Find first empty cell in column

It's this part

A1:A10=""

it's what is called an array formula, you can lookup array formula in help

http://www.cpearson.com/excel/ArrayFormulas.aspx


--


Regards,


Peo Sjoblom



"DW" wrote in message
...
Thanks everyone....

Now, this might be a dumb question, but why the ctrl+shift + enter combo
to commit the formula?


Don





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
Find empty cells, fill with text from cell above Marketer Excel Worksheet Functions 1 August 27th 07 10:35 PM
find first empty cell in column and start transpose next row in that cell ali Excel Discussion (Misc queries) 6 July 21st 07 11:55 PM
find last none empty cell kevcar40 Excel Discussion (Misc queries) 3 March 1st 06 11:59 AM
Find an empty cell and put a dash in it? markexcel Excel Worksheet Functions 5 October 12th 05 03:47 PM
Find a empty cell in next column Michael Excel Discussion (Misc queries) 3 June 15th 05 02:18 PM


All times are GMT +1. The time now is 09:31 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017