Formula challenge for the Excel wizzards. PLEASE HELP
Feb 21, 2012 at 12:33 PM
Posts365Registration dateMonday February 7, 2011StatusContributorLast seenSeptember 30, 2013
Feb 23, 2012 at 05:55 AM
This might be a long explanation, but only because I want to be clear about the problem.
I have 3 different Workbooks and the names are as follows:
1. Matrix Report
1.The Matrix report has amounts (example: R1000) from column "H1" all the way down to H7000.
2. Invoice has payment amounts (example: R100) from column "E1" all the way down to wherever E might end.
3. Calculator takes the outstanding amount, adds interest onto it and subtracts payment amount.
I want the value of "H" column in Matrix report workbook to be subtracted by the value in Invoice E and then the new value should be copied over or next to that H column. So here's the formula I have in mind: "MatrixH = MatrixH - InvoiceE + (InvoiceE÷100*10) + (MatrixH*15.5% ÷ 365*InvoiceJ) I used names along with columns to make it more understandable. InvoiceJ refers to a number of days that will be different for every month.
Is there a way to make Excel look up a certain reference and write the answer for that specific cell in the Matrix Report workbook???? Even if I need to add 7000 formulas refering to 7000 reference number I will do that. I just want to know if it's possible and how can I do it? Thanx in advance.