Formula challenge for the Excel wizzards. PLEASE HELP

[Closed]
Report
-
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
Hello,

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
2. Invoice
3. Calculator.

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.

1 reply

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
put some sort of data from each workbook,

and then re-explain your problem using data.