There are a total of 30 After 30 rows, which is the number that results
from
the Count-Search function, I get #NUM! Is there a workaround?

That's what this portion of the formula is for: (B2 should be absolute as I
noted in my other reply: B$2)

=IF(ROWS(C2:C$2)<=B2

If B2 holds this formula and the result is 30:

=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))

This expression: =IF(ROWS(C2:C$2)<=B2

Compares the number of rows the formula is copied to against the value in
B2. If the number of rows is greater than B2 then the formula returns a
blank. This is used as a form of error trap that is shorter and more
efficient than trapping errors when they occur from the SMALL function. If
there are 30 items that meet the criteria then SMALL(array,31) returns
#NUM!. The above expression is more efficient to use to trap these expected
errors.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
I think you are right, the formula returns a blank because the expressions
evaluate to be greater than the number returned by this formula:
=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))

There are a total of 30 After 30 rows, which is the number that results
from
the Count-Search function, I get #NUM! Is there a workaround?

--
RyGuy


"T. Valko" wrote:

I'm having a hard time trying to "visualize" where you're inserting rows.

A couple of observations:

Some of the references in the formula need to be absolute:

=IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"")

B2 should be B$2
A2 should be A$2

Also, depending on where you're inserting rows will change these
expressions:

=IF(ROWS(C2:C$2)
ROWS(C2:C$2)

If these expressions evaluate to be greater than the number returned by
this
formula:

=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))

Then the formula returns a blank. Is that what you mean by stopped
counting?

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
I have a follow-up question for T Valko.

Can you offer some kind of modification, or alternative, to the second
function (below) that you gave me yesterday?

In the form that you gave all functions to me, all functions worked
great!
However, I made some modifications, and now I have a problem here.

I used your first function, and ended up counting the number of
matches,
between items in my Column A on the active sheet, and items in Column A
on
the Import Sheet:
=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))
CSE, of course. This function is in my Column B of the active sheet,
and
this works fine.

Then, I used your second function, and found all cell addresses on the
Import Sheet, which is what I wanted:
=IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"")
This function is in my Column C.

Finally, I used your third function:
=INDIRECT("'Import Sheet'!"&E2)
This function is in my Column D.

I then inserted rows in Column A, sort of as place holders, for each of
the
numbers which are in Column B of the active sheet. This allowed me to
show
each of the cell addresses (Column C of my active sheet) and each bit
of
text
from these cell addresses (Column D of my active sheet). So, I thought
everything was going to be roses, but then I realized the second
function
stops counting when I inserted those rows in my Column A! Do you have
any
idea what could be causing this? Does anyone have an alternative to
look
up
cell addresses, such as Address & Vlookup? Tried a few combinations
of
things and nothing seemed to work.....


Cordially,
Ryan---


--
RyGuy