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

=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

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

=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

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

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 -

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

Thanks everyone....

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


Don

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

Ok, thanks........will this also work on a list that is not in any sort
order or does it have to be sorted?

Don

"Peo Sjoblom" wrote in message
...
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

Ok, did what you suggested.......but am getting a 0 value....below is my
current list (it of course grows every day), starting in A1. Now, with A19
being blank, the formula should return 8.50, but it's returning 0.

6.00
13.83
22.33
22.33
0.00
8.50
17.00
17.00
0.00
8.50
17.00
25.50
33.50
42.00
42.00
0.00
8.50


Don

"Peo Sjoblom" wrote in message
...
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