List of Due Dates from Part Payment events

[reynold]

Hello Experts,

I am looking to create a single list which remind me of upcoming payment due dates (Columns: Due Date, Amount, Vendor Name, Approved Date) (it will populate information from the below table which Payment Requestors and Accountants Fill)

Below Table shows,

Requestor Fills first 9 columns, and Accountants Pays and fills next 6 columns. It becomes difficult for my accountant to know which payment is due in the coming week or so on. I am figuring how can i create above said list of Due dates with columns Due Date, Amount, Vendor Name, Approved Date.

Any Guidance to help me start please.

Requestor

Accountant

Approved Dt

Vendor

T.Amount

Payment 1

Amt

Payment 2

Amt

Payment 3

Amt

Payment 1

Amt

Payment 2

Amt

Payment 3

Amt

10 Dec 15

ABC

10,000

17 Dec 15

5000

25 Dec 15

3000

28 Dec 15

2000

17 Dec 2015

5000

12 Dec 15

LMN

20,000

25 Dec 15

10000

28 Dec 15

10000

14 Dec 15

XYZ

5,000

5 Jan 16

5000

<tbody>
</tbody>

 

[AlanY]

this works with your sample data;

input your Vendor Name in Column S, and copy formula down

[B]Excel 2012[/B][TABLE]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[TH]L[/TH]
[TH]M[/TH]
[TH]N[/TH]
[TH]O[/TH]
[TH]P[/TH]
[TH]Q[/TH]
[TH]R[/TH]
[TH]S[/TH]
[TH]T[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Requestor[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Accountant[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Approved Dt[/TD]
[TD]Vendor[/TD]
[TD]T.Amount[/TD]
[TD]Payment 1[/TD]
[TD]Amt[/TD]
[TD]Payment 2[/TD]
[TD]Amt[/TD]
[TD]Payment 3[/TD]
[TD]Amt[/TD]
[TD]Payment 1[/TD]
[TD]Amt[/TD]
[TD]Payment 2[/TD]
[TD]Amt[/TD]
[TD]Payment 3[/TD]
[TD]Amt[/TD]
[TD="align: right"][/TD]
[TD]Due Date[/TD]
[TD] Amount[/TD]
[TD] Vendor Name[/TD]
[TD] Approved Date[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]10-Dec-15[/TD]
[TD]ABC[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]17-Dec-15[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]25-Dec-15[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]28-Dec-15[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]17-Dec-15[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25-Dec-15[/TD]
[TD="align: right"]3000[/TD]
[TD]ABC[/TD]
[TD="align: right"]10-Dec-15[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]12-Dec-15[/TD]
[TD]LMN[/TD]
[TD="align: right"]20,000[/TD]
[TD="align: right"]25-Dec-15[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]28-Dec-15[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25-Dec-15[/TD]
[TD="align: right"]10000[/TD]
[TD]LMN[/TD]
[TD="align: right"]12-Dec-15[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]14-Dec-15[/TD]
[TD]XYZ[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]05-Jan-16[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]05-Jan-16[/TD]
[TD="align: right"]5000[/TD]
[TD]XYZ[/TD]
[TD="align: right"]14-Dec-15[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Sheet1[/B][/COLOR][/CENTER]

[TABLE="width: 85%"]
<tbody>[TR]
[TD][B]Worksheet Formulas[/B][TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Q3[/TH]
[TD="align: left"]=IF([COLOR=Blue]K3+M3+O3=C3,"All Paid",IF([COLOR=Red]K3+M3+O3=0,D3,INDEX([COLOR=Green]D3:I3,MATCH([COLOR=Purple]INDEX([COLOR=Teal]J3:O3,MATCH([COLOR=#FF00FF]9.99999999999999E+307,J3:O3[/COLOR])-1[/COLOR]),D3:I3,0[/COLOR])+2[/COLOR])[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]R3[/TH]
[TD="align: left"]=IF([COLOR=Blue]K3+M3+O3=C3,0,INDEX([COLOR=Red]D3:I3,MATCH([COLOR=Green]Q3,D3:I3,0[/COLOR])+1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]T3[/TH]
[TD="align: left"]=INDEX([COLOR=Blue]A:A,MATCH([COLOR=Red]S3,B:B,0[/COLOR])[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

or this in Q3 also works

=IF(K3+M3+O3=C3,"All Paid",IF(K3+M3+O3=0,D3,INDEX(D3:I3,MATCH(MAX(J3,N3,L3),D3:I3,0)+2)))