Solved: Formula Challenge

I have Contracts from Contractors that receive scheduled payments (25% 50% 75% 100%) for the job. Sometimes the contract amount is increased (even after a payment or two is received). My SIMPLE formula changes payments already sent but they should remain static. Someone sent me this that (I believe) is on the right track but I don't understand it and am a little confused as to what is in the H Column and what the D Column is referencing.

This may be oversimplifying the problem but basically functions are volitile
ie they will recalculate every time that it precedents change. You
thereforte have to have different precedents for each formula:

in G1:G4 I put 25%, 50%, 75% & 100% respectively. Enter the contract value
as it increases in H1:H4.

then in the payments cells:

1st Payment:
=IF(H1,H1*G1,"")
2nd Payment:
=IF(H2,H2*G2-D6,"")
3rd Payment:
=IF(H3,H3*G3-SUM($D$6:D7),"")
Full Payment:
=IF(H4,H4*G4-SUM($D$6:D8),"")

Any Help would be appreciated....Thanks!

Steve

Hi StompS and welcome to VBAX!! Someone will be along to help you shortly I am sure.

I've added some comments to the sheet

Here is one way that seems to work.

Assumptions:

- G1:G4 contain the payments terms
- H1:H4 contain the amount, so H1 would have the start amount, H2 would hold the increased amount. If you wanted to increase the amount after two payments put it in H3, not H2.
- D1: D4 contain the payments due.

Use thse formulae
D1 is simply

Code:

---

=IF(H1,H1*$G1,"")

---

D2 is

Code:

---

=IF(H2="",D1,ROUND((H2-SUM($D$1:$D1))*$G2/SUM($G2:$G$4),2))

---

Copy D2 down to D3 and D4

Here is my challenge, I have a cell for the original contract amount and a
cell for any increase in the amount and (added together) have a cell for the
FULL contract amount (at that time). I can't have the current contract
amount typed in a sperate cell when the next payment is made.. They way I
have it set up is that there is my simple formula with SCHEDULED payments.
The don't add into the formula to amount that has been paid unless the date
field beside each payment <>"" which is the trigger to show payment and
deduct it from the current owed amount. I guess I need some type of formula
that, once the date field is triggered, the number becomes permanent and
will not change if the contract amount changes AFTER that check has been
paid.....am I dreaming?!?!? ; )

Thanks for all the help so far.....you answered my original question but it won't seem to do what I'm trying to accomplish....

StompS

You can't do it with a formula (not easily anyway), but you can do this with a little code. Please note that the code is very "fussy". Layout changes will require some recoding. If you need help with this, let us know.

Quote:

---

Originally Posted by StompS

Here is my challenge, I have a cell for the original contract amount and a
cell for any increase in the amount and (added together) have a cell for the
FULL contract amount (at that time). I can't have the current contract
amount typed in a sperate cell when the next payment is made.. They way I
have it set up is that there is my simple formula with SCHEDULED payments.
The don't add into the formula to amount that has been paid unless the date
field beside each payment <>"" which is the trigger to show payment and
deduct it from the current owed amount. I guess I need some type of formula
that, once the date field is triggered, the number becomes permanent and
will not change if the contract amount changes AFTER that check has been
paid.....am I dreaming?!?!? ; )

Thanks for all the help so far.....you answered my original question but it won't seem to do what I'm trying to accomplish....

StompS

---

Can you post your curren workbook so we can see what you have got?

goto sheet '4339 NE Alberta'

You can the 'GC' (C7) and Subs (E7, G7...) each need to get payments. The GC gets more percentage payments, the Subs get 25,50,75,100. The payment data (not SCHEDULED but PAID) gets pulled into the 'GCP' Sheet. The code worked that was sent, I viewed the code, I am now trying to decipher it and duplicate it so I have a 'Make Payment' button for each sub and the contractor.

As a side note....GCP!D7 has a challenge. I installed Ron de Bruin's calendar function and (I thought) it was supposed to pop up when you click that cell but it is not working. When you run the macro it works but the auto pop up does not work for some reason. I'm not sure what I did wrong.

Please excuse my ignorance. I feel kind of stupid here....was a CompSci major in '83 and understand code but it's been a long time and

1. The languages O learned are now dead
2. Even if they were still around I can't remember JACK!!!!

You guys have been great! Thank you.

I forgot to add that the scheduled payments for the subs (haven't formatted the GC's yet) does not get added into the 'Total Paid To Date' cell until the date cell next to the payment amount <> ""

Hi Stomps,
Calender1 is not included in your code. Is it an add-in? If so can you post it or an address for it.
Re triggering the payment code, are you happy with a button or is there a cell change which would trigger the macro.

I went into VBA and exported the modules and forms but they are not valid file formats for posting.

Here is a link to the site that I got it from:

http://www.rondebruin.nl/calendar.htm

In regards to the trigger, I want the box besde the scheduled payment clicked and then the calendar pops up and a date is selected. Now that the field <> "". That payment is counted as the presence of the date signifies that the check was written.

I am now "that guy".........UUUUGGGGHHHHHH!!!!!!

Hi Nels,
Here's a first attempt at the Alberta sheet. Enter the date in the yellow cell next to the required payment

With regard to the Calendar (Inseet/ObjectCalendar Control), it doesnt seem top work when mixed up with your frozen panes. The following should reposition the calendar clear of the breaks
[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CTop As Long, i As Long

If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("D1:D1"), Target) Is Nothing Then
For i = 4 To ActiveSheet.UsedRange.Rows.Count
If Not Intersect(Windows(1).VisibleRange, Rows(i)) Is Nothing Then
CTop = Rows(i).Top + 10
Exit For
End If
Next
Calendar1.Left = Target.Left + 10
Calendar1.Top = CTop
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub[/VBA]

Here's a non-VBA approach

Thanks to all whom responded with help, especially mdmackillop. He solved everything for me!!!! I can't begin to thank him enough! Again, thanks to all who helped.

StompS