automatic formula change in VBA

[quiskic]

Hello,

I am new to VBA so i'm not to sure of how this works, but what i need to do find a way to get my formulas done automatically because i have a big database.

The formula i have developed is this:
=SUMPRODUCT((Sheet2!A1:A67=3338)*(Sheet2!F1:F67={"Andre Lalande","Francis Wolfe","Claude Dugas","Pierre De Lafontaine"})*(Sheet2!G1:G67))

but i need the names to change according to a specific cell (eg. if C4=avionics, then the name list will be "Pat","Phil","Rick", but if C4=pneumatics, then the name list is....)

also this part must change: Sheet2!A1:A67=3338, where the number 3338 must vary according to a cell containing its exact name (eg. if B4=4567 the i want Sheet2!A1:A67=4567)

Is this possible to do?

Thx for your help

[pike]

can you post a smale sample workbook to play with?