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

ROW(B1) within the SMALL function denotes the 1st smallest, 2nd smallest etc;
and so that should be ROW(B1) instead of ROW(B152) which denotes 152nd
smallest

If this post helps click Yes
---------------
Jacob Skaria


"jc132568" wrote:

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

You can use this function to extract text from a cell:
Function RemDigits(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

Call it like this:
=RemDigits(A1)


HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Jacob Skaria" wrote:

ROW(B1) within the SMALL function denotes the 1st smallest, 2nd smallest etc;
and so that should be ROW(B1) instead of ROW(B152) which denotes 152nd
smallest

If this post helps click Yes
---------------
Jacob Skaria


"jc132568" wrote:

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

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