Make sure that this part of the formula...

INDEX('01.0A'!$A$1:$A$6

....is changed to...

INDEX('01.0A'!A$1:A$6

Therefore, your formulas should be as follows...

A1:

=SUMPRODUCT(--(COUNTIF('01.0B'!$A$1:$A$2,'01.0A'!$A$1:$A$6)=0))

B1, copied down and across:

=IF(ROWS(B$1:B1)<=$A$1,INDEX('01.0A'!A$1:A$6,SMALL (IF(COUNTIF('01.0B'!$A$
1:$A$2,'01.0A'!$A$1:$A$6)=0,ROW('01.0A'!$A$1:$A$6)-ROW('01.0A'!$A$1)+1),R
OWS(B$1:B1))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <GGfKe.399$U92.87@okepread06,
"Leslie Coover" wrote:

Thanks Domenic, I could not get the results I wanted, it simply displayed
the same id numbers
repeatedly, rather than including data in adjacent cells.

If I had really huge data sets your method is definitely superior as results
can be printed on one worksheet
and the "long list" and "short list" can be kept in two other worksheets.
I'm sure there is a way to extract data in adjacent cells along with primary
key data, but the data sets I am concerned with are not that large so I
decided to use Ron's filter method (in this thread). To use that method all
the data must be kept on one sheet (there may be a way to use multiple
sheets here too--but I do not know it). The advantage is that the extracted
records can contain data in adjacent cells along with the primary data key.

Les
"Domenic" wrote in message
...
Try...

B1, copied down and across:

=IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL( IF(COUNTIF('01.0B'!$A$1
:$A$2,'01.0A'!$A$1:$A$6)=0,ROW('01.0A'!$A$1:$A$6)-ROW('01.0A'!$A$1)+1),RO
WS(B$1:B1))),"")

Note that the column reference for...

INDEX('01.0A'!A$1:A$6

...has been changed to a relative reference.

Hope this helps!

In article <rf4Ke.353$U92.195@okepread06,
"Leslie Coover" wrote:

Okay so far so good
Now I have the "long" data in sheet 01.0A and the "short" data in
sheet 01.0B used basically the same SUMPRODUCT formula in sheet 01.0C

and used

=IF(ROWS($B$1:B1)<$A$1,INDEX('01.0A'!$A$1:$A$6,SMA LL(IF(COUNTIF('01.0B'!$A$
1:$
A$2,'01.0A'!$A$1:$A$6)=0,ROW('01.0A'!$A$1:$A$6)-ROW('01.0A'!$A$1)+1),ROWS($
B$1
:B1))),"")

This works allright, but it only lists the id # for each record, I want
all
the data for the whole record (column A to column D) what do I need to
change?

Here is an example, if want

W3245 peach 358 red
W2178 lemmon 548 yellow

but I only get

W3245
W2178

I know I could concatanate all the data in each record so it fits into
just
one cell, but is there an easier way?

Les