Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula / Macro Help
Hi, Can anyone help me? I have some text in cell A1 and i wish it to search in column A on 5 other worksheets for that reference. If it then finds a match on one of the sheets i want it to tell me on sheet1 in B1 that its in worksheet 4 for example ie sheet1 A1 = Toy Car sheet4 A335 = Toy Car sheet1 B1 = Sheet4 Can this be done?? Any help appreciated Regards Craig -- BSLAUTOMATION ------------------------------------------------------------------------ BSLAUTOMATION's Profile: http://www.excelforum.com/member.php...fo&userid=7611 View this thread: http://www.excelforum.com/showthread...hreadid=400755 |
#2
|
|||
|
|||
One way is to just look, and look again and look again...
Here's an example that just looks through 2 sheets: =IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),"sheet1", IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),"Sheet2", "neither")) (all one cell) You get 7 levels of looking until you have to use a different formula. BSLAUTOMATION wrote: Hi, Can anyone help me? I have some text in cell A1 and i wish it to search in column A on 5 other worksheets for that reference. If it then finds a match on one of the sheets i want it to tell me on sheet1 in B1 that its in worksheet 4 for example ie sheet1 A1 = Toy Car sheet4 A335 = Toy Car sheet1 B1 = Sheet4 Can this be done?? Any help appreciated Regards Craig -- BSLAUTOMATION ------------------------------------------------------------------------ BSLAUTOMATION's Profile: http://www.excelforum.com/member.php...fo&userid=7611 View this thread: http://www.excelforum.com/showthread...hreadid=400755 -- Dave Peterson |
#3
|
|||
|
|||
Cheers Dave, Great help! Is there another function other than MATCH - i was using the SEARCH function but i have to change A1 to the actual text ie "Toy Car" =IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),"sheet2", to =IF(ISNUMBER(SEARCH("Toy Car",Sheet2!A:A,0)),"sheet2", Reason im doing this is so that if someone put "Dinky Toy Car" into A1 it would still find "Toy Car" on sheet2 Any help?? Cheers Craig Dave Peterson Wrote: One way is to just look, and look again and look again... Here's an example that just looks through 2 sheets: =IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),"sheet1", IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),"Sheet2", "neither")) (all one cell) You get 7 levels of looking until you have to use a different formula. -- BSLAUTOMATION ------------------------------------------------------------------------ BSLAUTOMATION's Profile: http://www.excelforum.com/member.php...fo&userid=7611 View this thread: http://www.excelforum.com/showthread...hreadid=400755 |
#4
|
|||
|
|||
Can you strip the unnecessary text out of the cell (remove "dinky " in this
case)? And do you have rules that can be applied in general--always take the last two words???? BSLAUTOMATION wrote: Cheers Dave, Great help! Is there another function other than MATCH - i was using the SEARCH function but i have to change A1 to the actual text ie "Toy Car" =IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),"sheet2", to =IF(ISNUMBER(SEARCH("Toy Car",Sheet2!A:A,0)),"sheet2", Reason im doing this is so that if someone put "Dinky Toy Car" into A1 it would still find "Toy Car" on sheet2 Any help?? Cheers Craig Dave Peterson Wrote: One way is to just look, and look again and look again... Here's an example that just looks through 2 sheets: =IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),"sheet1", IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),"Sheet2", "neither")) (all one cell) You get 7 levels of looking until you have to use a different formula. -- BSLAUTOMATION ------------------------------------------------------------------------ BSLAUTOMATION's Profile: http://www.excelforum.com/member.php...fo&userid=7611 View this thread: http://www.excelforum.com/showthread...hreadid=400755 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
moving the formula "average" over one column in a macro | Excel Worksheet Functions | |||
macro with F9 | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Insert macro into formula | Excel Worksheet Functions | |||
Fill Formula - Macro | Excel Discussion (Misc queries) |