Another way - non-array:

=INDEX(B1:B100,MATCH(LOOKUP(2,1/(A1:A100<""),A1:A100),A1:A100)+1)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
Okay, I understand which value you want (the client code in Column B that
is
next to the first blank entry in Column A), but I'm still not completely
clear about your use of the word "choose". Assuming you mean you want a
formula that you can place in a cell which will show the client code you
are
looking for, then try this array-entered** formula...

=INDEX(B1:B200,MIN(ROW(A1:A200)*IF(A1:A200="",1,99 )))

** Commit this formula with Ctrl+Shift+Enter, not just Enter by itself.

Change the three occurrences of the number 200 to the largest row number
you
ever expect to have data in (I just took a guess at 200 rows of data
maximum).

Rick


"Nenagh" wrote in message
...
Hi Rick

Basically i have a column of sequential numbers, which are the invoice
numbers. When i open my invoice template i use one of these numbers and
indicate in the column next to it our client code.

What i would like to happen is for the template to recognize a used
number,
i.e the invoice number in column B has a client code net to it in say
column
A and chose the next available number, i.e a number in column B that has
no
client code next to it in column A.

Hope this makes sense?

Cheers
Nenagh

When
--
Nenagh


"Rick Rothstein (MVP - VB)" wrote:

Can you give us more than one example of what you are looking for and
show
us what should be "picked" for each of those examples? Also, exactly
what
do
you mean by "pick a number"... pick it where, show it how/where?

Rick


"Nenagh" wrote in message
...
I need a formula that will autmatically select a cell based on the
cell
next
to it being populated or not.

For example

A B C
XX 123
XX 124
125

I need a formula on my template which will pick a number (Our invoice
#)
in
cloumn B if the same row in column A is blank and will then choose
the
next
number in column B everytime column A is populated?

Any ideas?

Cheers
--
Nenagh