.. yet the zeros halt the whole process ..
The zeros returned are due to blank source cells. That's just the way Excel
calculates blank cells in formulas, eg: =Sheet2!A1 will return a zero if
Sheet2's A1 is blank
1. A simple way out, if it's more just for a neat look in the sheet is to
switch off zeros display via clicking Tools Options View tab Uncheck
"zero values" ok
2. If (1) is not feasible, then replace the formula in A2 with this:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$IV$1,0)),ROWS($A$1:A1))),"",IF(INDEX(WS1!A:A ,MATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1: $IV$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(W SN,WS1!$K$1:$IV$1,0)),0))=0,"",INDEX(WS1!A:A,MATCH (SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1, 0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1 !$K$1:$IV$1,0)),0))))
Recopy across/fill down, etc as before
The above is essentially just adding another IF trap for zero returns by
INDEX(...) to return blanks: "" instead
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---