Template

[Nenagh]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ragdyer]

Another way - non-array:

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