Complicated formula or macro need to add new sale into correct invoice?

[tonywatsonhelp]

Hi Everyone,

hope you can help

I need a formula or macro to add new sales to existing sales invoices.

we have clients that p[lace orders and pay over 16 instalmentsand offten add new items to the first order

When we get and order an invioce schedule is created with dates and amounts due.

Basicly this amount is in Range BK53:Bk68

Now any new sales are still split into 16 payments based on the same priciple and These are held in Range AY53:Ay68

Any Priviouse Adjustments are saved after BK so BL53:68 could hold amounts etc.

I have an Invioce total In range BI53:BI68 and this is the amount i need to add up

so Invices are set up
I get a new order 3 months in and some have been paid

So I need to identify when the new payments are going to get paid and add them to the payments already created.

In CD53:Cd68 I have if the invoice has already been sent as we can't add tp them (its a Yes or No)

If it is yes then that invoice amoount must be added to the next avaliable Invoice.

I'll do an example bellow hopefully that will help

	AY		AZ		BI																						CD
Row	New Sale Amounts		New Total Invoice Amounts		Current Total Invoice Amounts	this is the important amount as its the one I need to add the new sales to	First Sale	Old Adjustment	there might be more																		Invoice Created
53	100		because this invoice has already been created I can't change it so this amount roles over to the next payment		210		200	10																			yes
	100				210		200	10																			yes
	100				210		200	10																			yes
	100		610		210		200	10
	100		310		210		200	10
	100		310		210		200	10
	100		310		210		200	10
	100		310		210		200	10
	100		310		210		200	10
	100		310		210		200	10
	100		310		210		200	10
	100		310		210		200	10
	100		310		210		200	10
	100		310		210		200	10
	100		310		210		200	10
68	100		310		210		200

<tbody>
</tbody>

please help if you can thanks
Tony

 

[Rijnsent]

Hi Tony,
as a formula, something like this?
=IF(CD56="yes","",AY56+BI56+IF(CD55="yes",SUM(AY$53:AY55),0))
Cheers,
Koen

 

[tonywatsonhelp]

Thank you keon,
seams to do the job nicely