Data validation drop down list - vlookup / match criteria to display in list

In order to do it, you would have to name the sheets the same as the column E names, so we can find the sheet, then you would need to add another column to do the data validation choosing, then have the lookup formula in your cost/unit column. See attached.

Note the data validation assumes max 1000 rows in each supplier sheet.

The Data Validation formula applied to produce the lists is:

=INDEX(INDIRECT("'"&$E2&"'!C3:C1000"),MATCH($I2,INDIRECT("'"&$E2&"'!B3:B1000"),0)):INDEX(INDIRECT("'"&$E2&"'!C3:C1000"),MATCH(2,1/(INDIRECT("'"&$E2&"'!B3:B1000")=$I2)))

this also assumes that the invoice numbers are grouped together in the supplier sheets....

the formula to get unit price is:

=SUMPRODUCT(--(INDIRECT("'"&E2&"'!B2:B1000")=I2),--(INDIRECT("'"&E2&"'!C2:C1000")=J2),INDIRECT("'"&E2&"'!F2:F1000"))

or if you have Excel 2007 or later, you can use the more efficient:

=SUMIFS(INDIRECT("'"&E2&"'!F2:F1000"),INDIRECT("'"&E2&"'!B2:B1000"),I2,INDIRECT("'"&E2&"'!C2:C1000"),J2)