Steve,

Think we can revert to using the simpler entire col refs now that you've
exposed your layout

In your checksheet,

Put in A3:
=IF(dataprior!H3="","",IF(dataprior!H3="No Longer Here",ROW(),""))
(Ensure that A1:A2 are left blank)

Put in B3:
=IF(ROW(A1)COUNT(A:A),"",INDEX(dataprior!A:A,SMAL L(A:A,ROW(A1))))
Select A3:B3, fill down as far as required to cover the extent of source
data. It should work fine now.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steve" wrote in message
...
Well, this is getting kinda spooky.
The weird thing is that just like yesterday, it is working for the last 2
(
in rows 679 and 744) but not the first 2 (in rows 413 and 645).
Because of the same problems as yesterday, I thought maybe something was
wrong with the original formatted data on the dataprior sheet, so I
formatted
those cells all the same, but no change in the result.
My data is in rows 3:1000 on the data prior and data sheet. Here are the
formulas I'm using on the check sheet:
In A3, dragged to A1000:
=IF('Data Prior'!H3="","",IF('Data Prior'!H3="No Longer Here",ROW(),""))
In B3, dragged to B1000:
=IF(ROW(A3)COUNT(A:A),"",INDEX('Data
Prior'!$A$3:$A$1000,SMALL(A:A,ROW())))

Also, this might be the weird part. The references for the data sheet (new
employees) is doing the same thing. Giving the correct results for the
last 2
(in rows 595 and 608), but not in rows 549 and 514.
I entered these formulas on the check sheet in columns C & D, with these
dragged to 1000:
In C3, =IF(Data!H3="","",IF(Data!H3="New Employee",ROW(),""))
In D3,
=IF(ROW(C3)COUNT(C:C),"",INDEX(Data!$A$3:$A$1000, SMALL(C:C,ROW())))

I'm perplexed as to why it works great with the upper range data, but not
the lower range. Also, it's just a cooincidence that both data and data
prior
have 4 incidents. Each could be different on different weeks of data
download.

Thanks,

Steve