find the first blank cell in a range and return me it's position
 

Hi,

in my invoice sheet I want to look up on the sales sheet
the first blank cell in a range (c10:c500) and return me
the cell adjacent to the right of it i.e

the formual finds c20 is the first blank cell and it
returns me the cell to the right, the next invoice number
avaiable, 121

anyone any ideas how to achieve this please

thanks

steve

If there will be no filled cells in the range below the first blank
cell, one way:


=INDEX(D10:D500,COUNTA(C10:C500)+1)


OTOH, you might want to look at a different way of storing invoice
numbers:

http://www.mcgimpsey.com/excel/sequentialnums.html


In article ,
"steve alcock" wrote:

Hi,

in my invoice sheet I want to look up on the sales sheet
the first blank cell in a range (c10:c500) and return me
the cell adjacent to the right of it i.e

the formual finds c20 is the first blank cell and it
returns me the cell to the right, the next invoice number
avaiable, 121

anyone any ideas how to achieve this please

thanks

steve

many thanks for the advice, it works a trea.

steve




-----Original Message-----
If there will be no filled cells in the range below the
first blank
cell, one way:


=INDEX(D10:D500,COUNTA(C10:C500)+1)


OTOH, you might want to look at a different way of
storing invoice
numbers:

http://www.mcgimpsey.com/excel/sequentialnums.html


In article ,
"steve alcock" wrote:

Hi,

in my invoice sheet I want to look up on the sales
sheet
the first blank cell in a range (c10:c500) and return
me
the cell adjacent to the right of it i.e

the formual finds c20 is the first blank cell and it
returns me the cell to the right, the next invoice
number
avaiable, 121

anyone any ideas how to achieve this please

thanks

steve


.