Reference Cell Content

[batmanadk]

Hello, I want to create a table were the user only has to enter data, and only specific columns from the table and not to enter all the fields all the time. So, what the user basically does is enter ID, Cost, Quantity and payed amount. Based on those columns, TOTAL and DUE, could be calculated by formula. Since every ID number is an operation, then a lot of ID numbers can be entered in a period of time and that is why it is important the user doesn't need to look for the due amount or the payed amount. So this is the table that I want to generate. (I'm also attaching a table to the thread but I am trying to display an excel table to illustrate my point)



A B C D E F
1 ID unit cost quantity total payed Due
2 1 1 1 1 0.5 0.5
3 2 1 10 10 10 0
4 1 1 1 1 0.5 ******


So, I want to be able to create a formula in the F column (Due amounts), where I'd create an IF statement to see if an ID number in column A is being repeated, if True, then I need to calculate the row where the ID repetition happens and substract the payed amount in the last E column (Payed amount), from the matching Due Column (F) where the Id previously happened



In this case it would be, =IF A4 Matches any row in the same column (It matches cell A2) then substract that F cell (that would be the one in F2) of E4, and then you get 0. That way the user doesn't have to worry about looking for due amounts or payed amounts and he only enters what he gets in invoices provided

I'm attaching a sample file, so far I can't reference a cell in a given row and I was trying to combine the MATCH function with an IF statement to verify the ID number is being repeated.

[Ochimus]

batmanadk,

The only "criteria" in your sample is the ID in Col A.
Convert D2 to formula =[@[unit cost]]*[@quantity] and copy down so each row calculates the order cost automatically.
Then put this in F2, and copy down.

=SUMIF(A$2:A2,A2,D$2:D2)-SUMIF(A$2:A2,A2,E$2:E2)
(Adds up all the amounts invoiced to the ID in that row, and deducts what they have paid already. Balance goes in Col F.)

Test it by "paying" 1 in E4. Balance drops to 0.5 outstanding.

Add new order for same person, but pay 0.5 more than value in Col D, and Col F will become blank, as you have paid the arrears as well.

Ochimus