Formula / Macro Help

[BSLAUTOMATION]

Hi,

Can anyone help me?

Can i use a Formula or do i need a macro?

I want to Search text (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

Search Field - - sheet1 A1 = Toy Car

Information held - - sheet4 A335 = Toy Car

Answer - - sheet1 B1 = Sheet4

Can this be done??

Any help appreciated

Regards

Craig

[Dave Peterson]

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

[BSLAUTOMATION]

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





[QUOTE=Dave Peterson]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.

[Dave Peterson]

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