I have 2 separate workbooks. WB1 contains a list of invoices (Inv No in Col A, Job No in Col B & Amount in Col C) in a Table called "TabInvoices" on a sheet called "Invoices". There are sometimes more than one invoice per job.
e.g
Inv No Job No Amount
100 1250 £500
101 1261 £600
102 1245 £100
103 1250 £250
In the above dataset Job No 1250 has 2 invoices totalling £750 (Inv 100 & 103).
In my second workbook I want to type in a job No in Col A and the TOTAL of all invoices for that job number calculate in Col B. So in this example...
Job No Total
1250 750
I'm using SUMIFS as follows:-
=SUMIFS([WB1.xlsm]Invoices!$A$2:$C$1000,WB1.xlsm!TabInvoices[Job No],$A2)
...and I'm getting an error. I'm assuming it has to do with the syntax or am I using the wrong function? Both workbooks are open.
What is the obvious problem I'm creating for myself?
e.g
Inv No Job No Amount
100 1250 £500
101 1261 £600
102 1245 £100
103 1250 £250
In the above dataset Job No 1250 has 2 invoices totalling £750 (Inv 100 & 103).
In my second workbook I want to type in a job No in Col A and the TOTAL of all invoices for that job number calculate in Col B. So in this example...
Job No Total
1250 750
I'm using SUMIFS as follows:-
=SUMIFS([WB1.xlsm]Invoices!$A$2:$C$1000,WB1.xlsm!TabInvoices[Job No],$A2)
...and I'm getting an error. I'm assuming it has to do with the syntax or am I using the wrong function? Both workbooks are open.
What is the obvious problem I'm creating for myself?