Consolidate data on different sheets in a changing range
 

Hi all,
I receive 8 reports monthly, 1 for each area, with the all the activity
for that period. I would like to consolidate the data into one total
sheet. I have a master sheet with all the specialties where I would
like to have all the totals show. The activity will be different each
month so the the number of rows may change. The number of columns is
constant.

Below is what I have at the moment... but it cant be re-used as the
range is fixed. It also produces an N/A error if one produces an NA.

$B7 refers to a specialty code.
=VLOOKUP($B7,'Herstmere
PCT'!$B$5:$N$19,D$5,FALSE)+VLOOKUP($B7,Welhat!$B$5 :$N$20,D$5,FALSE)+VLOOKUP($B7,'N
Herts'!$B$5:$N$24,D$5,FALSE)+VLOOKUP($B7,'N
Herts'!$B$5:$N$24,D$5,FALSE)+VLOOKUP($B7,'SE
Herts'!$B$5:$N$20,D$5,FALSE)+VLOOKUP($B7,RBBS!$B$5 :$N$6,D$5,FALSE)+VLOOKUP($B7,W3R!$B$5:$N$27,D$5,FA LSE)+VLOOKUP($B7,Dacorum!$B$5:$N$22,D$5,FALSE)+VLO OKUP($B7,Dacorum!$B$5:$N$22,D$5,FALSE)

What is the best way to achieve this?