=HLOOKUP(Summary!F7,'[CPW(IBM) SD Weekly Report Week 43.xls]Wk43'!$N$3:$S$3,1)

The above works, however sheet which is WK43 in this example and the
workbook changes everyweek hence I need to change these values.

The workbook name will be in C2 and the worksheet will be in D2, however
when I try to build the function I keep getting syntax errors - any ideas -
thx