famres,
If you are happy to create the invoice 'manually' on a copy/paste basis then maybe this will help.
Given your attached file's sheets.....
Firstly, just as a one off, in the adjusted payments sheet clear B6 and re-enter the value 34.57
Enter the formula =LOOKUP(ABS(O7),'adjusted pay amts'!$B$6:$C$15) in cell Q7 and drag it down to Q25
That will look up your adjusted payments. You can then hide column O which has the Applied Payments so that it will not appear on printed invoice.
Otherwise to do away with column O in the invoice area. Copy the Applied Payments to say column AA which is outside your print area then change the LOOKUP formula to =LOOKUP(ABS(AA7),'adjusted pay amts'!$B$6:$C$15)
If you always have the same max number of line items (19) then you can leave all the formulas permanently in column Q. Otherwise find a routine that suits.
Hope that helps.
|