A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Looking up next available number



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 11:52 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 303
Default 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.
  #2  
Old June 4th, 2010, 12:39 AM posted to microsoft.public.excel.worksheet.functions
Don Guillett[_2_]
external usenet poster
 
Posts: 607
Default Looking up next available number

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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.


  #3  
Old June 4th, 2010, 01:21 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default 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.


  #4  
Old June 4th, 2010, 09:34 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 303
Default 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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:50 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.