|
Looking up next available number
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. |
Looking up next available number
Try this.
in T2: =MAX(INDEX(COLUMN($C2:$R2)*($C2:$R20),))- 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" wrote in message ... 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. |
Looking up next available number
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. "Steve Dunn" wrote: Try this. in T2: =MAX(INDEX(COLUMN($C2:$R2)*($C2:$R20),))- 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" wrote in message ... 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. |
| All times are GMT +1. The time now is 07:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com