|
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 06:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com