I have 2 worksheets - Jobs & Invoices
Within the job sheet I have 3 columns at the end that display the value for the job and the value for the invoice against that job, then a difference calculated. This allows me to ensure my job fees and what I invoice match each other. I'm looking for the difference to always be zero - anything that isnt is coloured by CF. See below
I'm using a basic formual to bring across the Job Fee value fromt the TOTAL FEE column based on a few criteria (which aren't important for the purposes of this problem) - This works fine and the values are always correct. I'm then using a SUMPRODUCT formula to bring across the invoice value from the invoice sheet based on the invoice numbers in INV NO1 & INV NO2 columns etc. The formula I'm using is:-
=IF(SUMPRODUCT((InvoiceTable[Job No]=[@[Job No]])*((InvoiceTable[Type]="INV")+(InvoiceTable[Type]="WO"))*(InvoiceTable[Amount]))))
The invoice table is as follows:-
Everything works fine until we invoice more than one job on a single invoice and then my formula doesn't split the invoice amount accordingly.
The example above highlights this for Jobs 10526, 10527, 10531, 10533, 10534 - each have different values but combine to a total of £2675 (invoice number 10287). How can I get this figure to be proportioned correctly to each job in the job list so that my difference column reads correctly? At present I enter 1 job number against an invoice (and that's obviously the problem). I can't go away from one row for one job and one row for one invoice.
I hope I've explained the conundrum well enough to be understaood.
Any thoughts?
Within the job sheet I have 3 columns at the end that display the value for the job and the value for the invoice against that job, then a difference calculated. This allows me to ensure my job fees and what I invoice match each other. I'm looking for the difference to always be zero - anything that isnt is coloured by CF. See below
I'm using a basic formual to bring across the Job Fee value fromt the TOTAL FEE column based on a few criteria (which aren't important for the purposes of this problem) - This works fine and the values are always correct. I'm then using a SUMPRODUCT formula to bring across the invoice value from the invoice sheet based on the invoice numbers in INV NO1 & INV NO2 columns etc. The formula I'm using is:-
=IF(SUMPRODUCT((InvoiceTable[Job No]=[@[Job No]])*((InvoiceTable[Type]="INV")+(InvoiceTable[Type]="WO"))*(InvoiceTable[Amount]))))
The invoice table is as follows:-
Everything works fine until we invoice more than one job on a single invoice and then my formula doesn't split the invoice amount accordingly.
The example above highlights this for Jobs 10526, 10527, 10531, 10533, 10534 - each have different values but combine to a total of £2675 (invoice number 10287). How can I get this figure to be proportioned correctly to each job in the job list so that my difference column reads correctly? At present I enter 1 job number against an invoice (and that's obviously the problem). I can't go away from one row for one job and one row for one invoice.
I hope I've explained the conundrum well enough to be understaood.
Any thoughts?
