I don't know if it's possible using worksheet formulas--well, I know it's not
possible for me!

You could post your question in .worksheet.functions to see if anyone can help.
(Those people live for this kind of stuff.) And after seeing that "B3 = TOYS &
BABY", I'm not sure if it could be done in a formula.

Me on the other hand, I'd cheat and use a UserDefined function.

Do you want to try that?

If yes, paste this code into a general module of your workbook (more about that
later).

Option Explicit
Function mySearch(mySearchCell As Range, ParamArray myRng()) As String

Dim myCell As Range
Dim myRealRng As Range
Dim myElement As Variant
Dim myStr As String

myStr = ""

For Each myElement In myRng
If TypeOf myElement Is Range Then
'do the work
Set myRealRng = Nothing
On Error Resume Next
Set myRealRng = Intersect(myElement, myElement.Parent.UsedRange)
On Error GoTo 0

If myRealRng Is Nothing Then
'do nothing
Else
For Each myCell In myRealRng.Cells
If IsEmpty(myCell) Then
'do nothing
Else
If InStr(1, mySearchCell, myCell.Value, _
vbTextCompare) 0 Then
myStr = myStr & " & " & myRealRng.Parent.Name
Exit For
End If
End If
Next myCell
End If
End If
Next myElement

If myStr = "" Then
myStr = "Not Found!"
Else
myStr = Mid(myStr, 4)
End If

mySearch = myStr

End Function


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=mysearch(A1,Toys!A:A,baby!A:A,'Other Sheet!A:A)




atxcomputers wrote:

Hi,

Im BSLAUTOMATION but @ Home and cant reply to a thread because i forgot
my works password. Anyway

Dave Peterson Wrote:

Can you strip the unnecessary text out of the cell (remove "dinky " in
this
case)?

No because "Dinky" could be anything from a number or random text, see
below for more details

And do you have rules that can be applied in general--always take the
last two
words????

Not to sure at the moment but im trying to find a way and ill explain a
bit better of what im doing!

Eg

Sheet1 name = SORT (Main Page with formula in column B)

Sheet2 name = TOYS
Sheet2 A1 = "Toy Car"
Sheet2 A2 = "Toy Plane"

Sheet3 name = BABY
Sheet3 A1 = "Rattle"
Sheet3 A2 = "Blanket"

*plus 4 other Sheets i wish it to check (6 total)

MainPage with column A as the input
A1 = 1234 Toy Car Dinky B1 = TOYS
A2 = Plastic Rattle WL78 B2 = BABY
**A3 = Blanket with Toy Car B3 = TOYS & BABY


**This could be my only problem because there are 2 keywords on
different sheets

I think its going to need a macro as the Search formula is what im
after but i want to use a reference like (A1) instead of having to use
actual text or "keyword"

Can i change the reference or value of A1 and name it AA and then use
that in the formula or im i back to the macro??

Anyhelp please ?

Cheers

Craig

--
atxcomputers
------------------------------------------------------------------------
atxcomputers's Profile: http://www.excelforum.com/member.php...o&userid=26852
View this thread: http://www.excelforum.com/showthread...hreadid=400972

--

Dave Peterson