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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

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,181,096
Messages
5,928,048
Members
436,586
Latest member
latintxn

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