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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Requestor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Accountant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[/TR]
[TR]
[TD]10 Dec 15[/TD]
[TD]ABC[/TD]
[TD]10,000[/TD]
[TD]17 Dec 15[/TD]
[TD]5000[/TD]
[TD]25 Dec 15[/TD]
[TD]3000[/TD]
[TD]28 Dec 15[/TD]
[TD]2000[/TD]
[TD]17 Dec 2015[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12 Dec 15[/TD]
[TD]LMN[/TD]
[TD]20,000[/TD]
[TD]25 Dec 15[/TD]
[TD]10000[/TD]
[TD]28 Dec 15[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14 Dec 15[/TD]
[TD]XYZ[/TD]
[TD]5,000[/TD]
[TD]5 Jan 16[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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)))