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
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by Dave Peterson
Sorted already
Bookmarks