Return a cell address
 

I am trying to return a cell address based on a portion of an alphanumeric
string.

I have a column of values on tab A that lists values like B1, B235, C3, BR4,
etc. I want to return the address of the first cell containing
=SEARCH("B***",A:A).

Ultimately my goal is to find the search() above in column A on tab B and
use that as a starting point for bringing those values on tab A in to tab B.
From there I can do a Vlookup() to import the remainder of the columns I need
by creating a named range.

I know I have done this before, but I can't find the file that I created
this function in. I am not a big fan of VB and would like to avoid this. Can
anyone help me? I would sincerely appreciate it.

Thank You

Frank Pytel

Return a cell address
 

try
=MATCH("b*",a:a,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Frank Pytel" wrote in message
...
I am trying to return a cell address based on a portion of an alphanumeric
string.

I have a column of values on tab A that lists values like B1, B235, C3,
BR4,
etc. I want to return the address of the first cell containing
=SEARCH("B***",A:A).

Ultimately my goal is to find the search() above in column A on tab B and
use that as a starting point for bringing those values on tab A in to tab
B.
From there I can do a Vlookup() to import the remainder of the columns I
need
by creating a named range.

I know I have done this before, but I can't find the file that I created
this function in. I am not a big fan of VB and would like to avoid this.
Can
anyone help me? I would sincerely appreciate it.

Thank You

Frank Pytel

Return a cell address
 

Don;

That was a pretty good start. I am still lacking in my coding though. I am
getting #REF AND #VALUE errors. This is what I have come up with.

=INDEX(Import!D2:E65536,MATCH("B*",Import!D2:D6553 6,0)+1,4,Import!D2:D65536)

=INDEX(Import!D2:E65536,MATCH("B*",Import!D2:D6553 6,0)+1,4,Import!D2:D65536)

The Match()+1 returns the cell address. Now I am trying to get the
information out of that cell. Problem is that it is returning a value from
the tab I am on. The references are B1, etc. almost always B###. I need the
text B111, or B1 or whatever.

I even tried concatenating it into a formula

=CONCATENATE("=","Import!",INDIRECT(ADDRESS(MATCH( "B*",Import!D2:D65536,0)+1,COLUMN(Import!D2:D65536 ),4)))

I have tried it with and without the "=" first reference and tried a couple
of Indirect() calls as well.

Any ideas?

Thanks for all your help thus far. It is very helpful.

Frank Pytel



"Don Guillett" wrote:

try
=MATCH("b*",a:a,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Frank Pytel" wrote in message
...
I am trying to return a cell address based on a portion of an alphanumeric
string.

I have a column of values on tab A that lists values like B1, B235, C3,
BR4,
etc. I want to return the address of the first cell containing
=SEARCH("B***",A:A).

Ultimately my goal is to find the search() above in column A on tab B and
use that as a starting point for bringing those values on tab A in to tab
B.
From there I can do a Vlookup() to import the remainder of the columns I
need
by creating a named range.

I know I have done this before, but I can't find the file that I created
this function in. I am not a big fan of VB and would like to avoid this.
Can
anyone help me? I would sincerely appreciate it.

Thank You

Frank Pytel

Return a cell address
 

Perhaps you are over complicating. Look again in the help index for INDEX.
Returns b2222
c1
b1
b2222
xxxx


=INDEX(Sheet23!J:J,MATCH("b*",Sheet23!J:J,0)+1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Frank Pytel" wrote in message
...
Don;

That was a pretty good start. I am still lacking in my coding though. I am
getting #REF AND #VALUE errors. This is what I have come up with.

=INDEX(Import!D2:E65536,MATCH("B*",Import!D2:D6553 6,0)+1,4,Import!D2:D65536)

=INDEX(Import!D2:E65536,MATCH("B*",Import!D2:D6553 6,0)+1,4,Import!D2:D65536)

The Match()+1 returns the cell address. Now I am trying to get the
information out of that cell. Problem is that it is returning a value from
the tab I am on. The references are B1, etc. almost always B###. I need
the
text B111, or B1 or whatever.

I even tried concatenating it into a formula

=CONCATENATE("=","Import!",INDIRECT(ADDRESS(MATCH( "B*",Import!D2:D65536,0)+1,COLUMN(Import!D2:D65536 ),4)))

I have tried it with and without the "=" first reference and tried a
couple
of Indirect() calls as well.

Any ideas?

Thanks for all your help thus far. It is very helpful.

Frank Pytel



"Don Guillett" wrote:

try
=MATCH("b*",a:a,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Frank Pytel" wrote in message
...
I am trying to return a cell address based on a portion of an
alphanumeric
string.

I have a column of values on tab A that lists values like B1, B235, C3,
BR4,
etc. I want to return the address of the first cell containing
=SEARCH("B***",A:A).

Ultimately my goal is to find the search() above in column A on tab B
and
use that as a starting point for bringing those values on tab A in to
tab
B.
From there I can do a Vlookup() to import the remainder of the columns
I
need
by creating a named range.

I know I have done this before, but I can't find the file that I
created
this function in. I am not a big fan of VB and would like to avoid
this.
Can
anyone help me? I would sincerely appreciate it.

Thank You

Frank Pytel

Return a cell address
 

OOhhh, Hooo Don;

"...forest for the trees!" Don't ya know.

Brilliant. I've done this so many times. I need to stop trying to force
Excel and just let it do it's job.

Thank you, Don.

God Bless

Frank Pytel


"Don Guillett" wrote:

Perhaps you are over complicating. Look again in the help index for INDEX.
Returns b2222
c1
b1
b2222
xxxx


=INDEX(Sheet23!J:J,MATCH("b*",Sheet23!J:J,0)+1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Frank Pytel" wrote in message
...
Don;

That was a pretty good start. I am still lacking in my coding though. I am
getting #REF AND #VALUE errors. This is what I have come up with.

=INDEX(Import!D2:E65536,MATCH("B*",Import!D2:D6553 6,0)+1,4,Import!D2:D65536)

=INDEX(Import!D2:E65536,MATCH("B*",Import!D2:D6553 6,0)+1,4,Import!D2:D65536)

The Match()+1 returns the cell address. Now I am trying to get the
information out of that cell. Problem is that it is returning a value from
the tab I am on. The references are B1, etc. almost always B###. I need
the
text B111, or B1 or whatever.

I even tried concatenating it into a formula

=CONCATENATE("=","Import!",INDIRECT(ADDRESS(MATCH( "B*",Import!D2:D65536,0)+1,COLUMN(Import!D2:D65536 ),4)))

I have tried it with and without the "=" first reference and tried a
couple
of Indirect() calls as well.

Any ideas?

Thanks for all your help thus far. It is very helpful.

Frank Pytel



"Don Guillett" wrote:

try
=MATCH("b*",a:a,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Frank Pytel" wrote in message
...
I am trying to return a cell address based on a portion of an
alphanumeric
string.

I have a column of values on tab A that lists values like B1, B235, C3,
BR4,
etc. I want to return the address of the first cell containing
=SEARCH("B***",A:A).

Ultimately my goal is to find the search() above in column A on tab B
and
use that as a starting point for bringing those values on tab A in to
tab
B.
From there I can do a Vlookup() to import the remainder of the columns
I
need
by creating a named range.

I know I have done this before, but I can't find the file that I
created
this function in. I am not a big fan of VB and would like to avoid
this.
Can
anyone help me? I would sincerely appreciate it.

Thank You

Frank Pytel

Return a cell address
 

Glad to help. As many know I am a proponent of the KISS principle

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Frank Pytel" wrote in message
...
OOhhh, Hooo Don;

"...forest for the trees!" Don't ya know.

Brilliant. I've done this so many times. I need to stop trying to force
Excel and just let it do it's job.

Thank you, Don.

God Bless

Frank Pytel


"Don Guillett" wrote:

Perhaps you are over complicating. Look again in the help index for
INDEX.
Returns b2222
c1
b1
b2222
xxxx


=INDEX(Sheet23!J:J,MATCH("b*",Sheet23!J:J,0)+1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Frank Pytel" wrote in message
...
Don;

That was a pretty good start. I am still lacking in my coding though. I
am
getting #REF AND #VALUE errors. This is what I have come up with.

=INDEX(Import!D2:E65536,MATCH("B*",Import!D2:D6553 6,0)+1,4,Import!D2:D65536)

=INDEX(Import!D2:E65536,MATCH("B*",Import!D2:D6553 6,0)+1,4,Import!D2:D65536)

The Match()+1 returns the cell address. Now I am trying to get the
information out of that cell. Problem is that it is returning a value
from
the tab I am on. The references are B1, etc. almost always B###. I need
the
text B111, or B1 or whatever.

I even tried concatenating it into a formula

=CONCATENATE("=","Import!",INDIRECT(ADDRESS(MATCH( "B*",Import!D2:D65536,0)+1,COLUMN(Import!D2:D65536 ),4)))

I have tried it with and without the "=" first reference and tried a
couple
of Indirect() calls as well.

Any ideas?

Thanks for all your help thus far. It is very helpful.

Frank Pytel



"Don Guillett" wrote:

try
=MATCH("b*",a:a,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Frank Pytel" wrote in message
...
I am trying to return a cell address based on a portion of an
alphanumeric
string.

I have a column of values on tab A that lists values like B1, B235,
C3,
BR4,
etc. I want to return the address of the first cell containing
=SEARCH("B***",A:A).

Ultimately my goal is to find the search() above in column A on tab
B
and
use that as a starting point for bringing those values on tab A in
to
tab
B.
From there I can do a Vlookup() to import the remainder of the
columns
I
need
by creating a named range.

I know I have done this before, but I can't find the file that I
created
this function in. I am not a big fan of VB and would like to avoid
this.
Can
anyone help me? I would sincerely appreciate it.

Thank You

Frank Pytel