Looking up next available number

[Nadine]

I have columns numbered for payments. A PO can have any number of payments.
A PO can be shown on any number of rows as the resource being billed against
the PO can be any umber of resources.
I need to write a formula that will tell me the next available payment
number per PO. So if PO1 Resource 1 is paid, either in part or in full, then
the next available pmt number is 2. Or if PO1 Resource 2 is paid but
Resource 1 is not paid, then still the next available pmt number is 2 as the
PO has already used pmt 1. Does that make sense?

Example:
Col A = PO#
Col B = PO Line item to pay against
Col C = Pmt 1
Col D = Pmt 2
Col E = Pmt 3
etc to currently 16 possible payments.

Row 2 = PO1 Resource1
Row 3 = PO1 Resource2
Row 4 = PO1 Resource 3
Row 5 = PO2 Resource1
Row 6 = PO3 Resource1
Row 7 = PO3 Resource2
Row 8 = PO3 Resource3
Row 9 = PO2 Resource2

Assume Rows 4, 7, 8 are paid all against payment 1. The next payment number
available for PO1 is 2, PO2 is 1, PO3 is 2. I need this number to appear on
each line, not in some summary sheet. I hope this makes sense. Thanks to
anyone who can help me with this.

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Steve Dunn]

Try this.

in T2:

=MAX(INDEX(COLUMN($C2:$R2)*($C2:$R2>0),))-
CELL("COL",$C$2:$R$2)+1

in U2:

=MAX(INDEX(($A$2:$A$100=$A2)*$T$2:$T$100,))+1

Copy T2:U2 down as far as required. Column U gives you your answer, you can
hide column T.


HTH
Steve D.

 

[Nadine]

Unfortunately the real data has the date separated by 4 columns. I can
usually modify a formula for my specific needs without having to put all of
the information into the discussion. People would never read it otherwise.
Thanks for the info, though.