I think I need a macro, but would rather a formula

JamesKM

Board Regular
Joined
Aug 12, 2002
Messages
130
Is there a formula I can put in column F that will fill in the relevant payment stage in this example spreadsheet. The trouble I have had is that the formula needs to pick up a lot of variables, namely:
Whether the company has paid
Whether the customer has completed all payments
Whether the due date is less than today()
etc.

I hope the example below is self explanatory as to the payment stages required.

If I need to write a macro to evaluate the sheet please could you help with that (although I would prefer a formula)
Book2
ABCDEFGHI
1CompanyNameAmountDueDateDueAmountPaidDatePaidPaymentStagePaymentStagesTodaysDate
2Company150.0001/10/200250.0001/10/2002AwaitingPayment10/10/2002
3Company150.0001/11/2002OverduePayment
4Company150.0001/12/2002PaymentReceived(awaitingOthers)
5Company150.0001/01/2003PaidinFull
6Company150.0001/02/2003
7Company150.0001/03/2003
8Company275.0001/10/200175.0001/10/2001
9Company275.0001/12/200175.0001/12/2001
10Company275.0001/01/200275.0001/01/2002
11Company275.0001/02/200275.0001/02/2002
12Company275.0001/03/200275.0001/03/2002
13Company275.0001/04/200275.0001/04/2002
14Company3100.0015/11/2001100.0015/11/2001
15Company3100.0015/12/2001100.0015/12/2001
16Company3100.0015/01/2002100.0015/01/2002
17Company3100.0015/02/2002100.0015/02/2002
18Company3100.0015/03/2002100.0015/03/2002
19Company3100.0015/04/2002100.0015/04/2002
20Company3100.0015/05/2002100.0015/05/2002
21Company3100.0015/06/2002100.0015/06/2002
22Company3100.0015/07/2002100.0015/07/2002
23Company3100.0015/08/2002100.0015/08/2002
24Company3100.0015/09/2002100.0015/09/2002
25Company3100.0015/10/2002
26Company3100.0015/11/2002
Sheet1


[ This Message was edited by: JamesKM on 2002-10-10 10:35 ]</font>
This message was edited by JamesKM on 2002-10-10 10:56
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Not entirely sure if this was what you were after (my today() is in H1)

=IF(AND(B2=D2,SUMIF($A$2:$A$13,$A2,$B$2:$B$13)>SUMIF($A$2:$A$13,$A2,$D$2:$D$13)),"Payment Received (Awaiting Others)",IF(AND(B2>D2,$H$1>$C2),"Overdue Payment",IF(B2>D2,"Not Paid","Paid in Full")))
 

Forum statistics

Threads
1,143,640
Messages
5,719,987
Members
422,257
Latest member
Calion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top