Have a look at the attached immage.

The array formula I have moved on sheet2 because it doesn't accept to
be placed in merged cells. The answers are dicerctly coppied by
=sheet2!B20 etc back to sheet 1 into the merged cells. The formula
needly summarizes the diameters (numbers) used on the sheet in acsending
order (column F).

My problem is the following:

When as is shown on the immage there are only 5 diameters used
(8,10,12,16 and 20) the remaining cells are empty. Together with R and Y
on the left the sumproduct function I calculate the total length, within
this cell the diameter is used to lookup the weight per length. Vlookup
cannot lookup an empty cell so the #N/A is displayed, this messes up my
totals which now also display as #N/A.

I would be able to solve the problem by retruning a 0 (zero) value in
either sheet1 or sheet2, in the lookup table I added the 0 diameter
which will retrun 0 (0 x 0 = 0). This way my summary table will stay in
tact. By using the custom format I mentioned in the first post of this
thread these 0 will not be displayed and will show nothing.


W:o:


+-------------------------------------------------------------------+
|Filename: immage 1.jpg |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=141|
+-------------------------------------------------------------------+

--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98209