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.
