Consolidate and Lookup

[ciara_daniels]

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,FALSE)+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

 

[Pete_UK]

As you are looking for exact matches, you can make the ranges much
larger than you would expect (eg $B$5:$N$100), so that you do not have
to adjust them each time you do this.

A common way of avoiding #N/A is a formula along the lines of:

=IF(ISNA(vlookup_formula),0,vlookup_formula)

However, as you have 9 vlookups (or should it be 8 ? - your final one
is repeated), that would be a bit clumsy.

Would it be possible to return the values from the other sheets into
individual columns (which could be hidden) and then to sum these
columns to give you the value you have now? In other words, you would
have this formula in column D (say):

=IF(ISNA(VLOOKUP($B7,'Herstmere
PCT'!$B$5:$N$100,D$5,FALSE),0,VLOOKUP($B7,'Herstmere
PCT'!$B$5:$N$100,D$5,FALSE))

and this in column E:

=IF(ISNA(VLOOKUP($B7,Welhat!$B$5:$N$100,D$5,FALSE),0,VLOOKUP($B7,Welhat!$B$5:$N$100,D$5,FALSE))

and so on for the other sheets. A simple =SUM(D7:K7) in L7 would give
you the consolidated total, with columns D to K hidden.

Hope this helps.

Pete