Use the ROWS function instead of the ROW function and use the cell reference
of the cell in which the formula is being entered.

For example, if the formula is being entered in cell B152 and then copied
down, use:

ROWS(B$152:B152)

--
Biff
Microsoft Excel MVP


"jc132568" wrote in message
...
I am using the following to pull out data based on the presence of text in
a
particular column:
In B2
=IF(ISERROR(SMALL($F:$F,ROW(B1))),"
",INDEX(PC3005!B:B,MATCH(SMALL($F:$F,ROW(B1)),$F:$ F,0)))
In F2
and =IF(TRIM(PC3005!Y2)<"",ROW()," ")

It works great and is part of our everyday sheets. I am adapting this for
another worksheet application where I want to achieve this same function
but
further down the worksheet, say in B152 and F152. The formula doesn't
work!
Do I have to have the formula at the top of the worksheet for it to work.
What have I missed?

=IF(ISERROR(SMALL($F:$F,ROW(B152))),"
",INDEX(AntibacterialDrugs!A:A,MATCH(SMALL($F:$F,R OW(B152)),$F:$F,0)))

=IF(TRIM(AntibacterialDrugs!O2)<"",ROW()," ")

Thanks
jc