Just a bit of caution, you realize that your solution is very complicated.
Maybe you should rethink your layout. What happens if someone else inherits
this spreadsheet or if MS decides to close down these newsgroups. There is
nobody working at MS support that can audit that formula.


--


Regards,


Peo Sjoblom




"RyGuy" wrote in message
...
A few days ago Biff gave me a function and it seemed to work great at
first,
but then it didn't seem to really work. I've pretty much narrowed it down
to
this. If the value in column B is different (from the value below), then
I
have to tell the below function to change the new reference to the cell
directly to the left (which is Column B), but if the value in Column B is
the
same (as value above), then I have to tell the function to absolute
reference
the cell above and continue down to the next cell (this seems to be the
genesis of the problem):

=IF(B2=B3,IF(ROWS(B$2:B2)<=B$1,"A"&SMALL(IF(ISNUMB ER(SEARCH(A3,'Import
Sheet'!A$1:A$65000)),ROW('Import
Sheet'!A$1:A$65000)),ROWS(OFFSET(B$2:B2,0,-1))),""),IF(ROWS(OFFSET(B2,0,-1))<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A3,'Import
Sheet'!A$1:A$65000)),ROW('Import
Sheet'!A$1:A$65000)),ROWS(OFFSET(B2,0,-1)))))
Note: this is a CSE function

For instance, the values in B2:B9 are all the same, so B$2:B2 works fine,
down to B9. The value in B9 is different from the value in B10, so I have
to
get the function to absolute reference B10, and move down until the value
in
Column B changes again, which happens at B11. Then B11:B14 are the same
and
then it changes once again at B15:16 (these two are the same). This has
me
baffled. I hate to be annoying and keep posting this same, although
slightly
modified, question, but I'm totally out of ideas. Any thoughts? Anyone?

Thanks so much!
Ryan--






"T. Valko" wrote:

Sent you an email.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
This is a follow up to a post that I submitted here a few days ago. T.
Valko
gave me a great function and tried, repeatedly, to help me (thanks for
the
assistance). No matter what I did, I just couldn't get the function
working
the way I needed it to work. Thus, I am back with a similar request.
I
am
hoping someone can give me a function to return the cell address for
all
items in a list that match a certain criteria. I am thinking of
something
such as this:
=ADDRESS(MATCH(A2,'Import Sheet'!A2:A65000,0),1)

This works, but the value in A2 must be an exact match with the value
in
the
list (which is 'Import Sheet'!A2:A65000). I know this comes from the
'match_type' in MATCH(lookup_value,lookup_array,match_type)

The problem is that the value in A2 will be a name, but the values in
the
list in 'Import Sheet'!A2:A65000, are names, combined with spaces
before
and
after the names, commas before and after the names, hyphens before and
after
the names, etc. Since the data will never be an EXACT MATCH, I can't
do
the
match. I'm thinking the SEARCH function will be the best function to
use
because it does not rely on case sensitive data. A function or a macro
would
be great!!

Does anyone have any insight about this?

Cordially,
Ryan--



--
RyGuy