| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a cell address
|
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| USING VLOOKUP TO RETURN A CELL ADDRESS | Excel Worksheet Functions | |||
| LOOKUP & RETURN CELL ADDRESS | Excel Worksheet Functions | |||
| Lookup, and Return Cell Address | Excel Worksheet Functions | |||
| V Lookup and return cell address | Excel Worksheet Functions | |||
| How do I use a function to return the address of a cell? | Excel Worksheet Functions | |||
Hybrid Mode
