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

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

Dave,

Absolutly fantastic
you are amazing
This will save me hours of sorting these lists
Thanks everso much
I thought it couldnt be done using a formula - well i cant do anyway -
theres probably some guru out there with that knowledge.

Thanks once again

Craig


Dave Peterson Wrote:
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


--
BSLAUTOMATION
------------------------------------------------------------------------
BSLAUTOMATION's Profile: http://www.excelforum.com/member.php...fo&userid=7611
View this thread: http://www.excelforum.com/showthread...hreadid=400972

Hi

The below U/D function matches a string of characters from a given cell
to text on other sheets and then tells me which sheets it is on.

I would like to find out what it has found but cant seem find how to
get that result.

I have highlighted the bit that does the compare and feel it has
something to do with this


Dave Peterson Wrote:


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

I tried changing myRealRng.Parent.Name but just came back with VALUE#

Hopefully Dave Peterson will help me as he did the above function but
if anyone else can help then please do so


Thanks in advance

Regards

Craig


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

This line builds the string to return:

myStr = myStr & " & " & myRealRng.Parent.Name

Maybe you could just change it to:

myStr = myStr & " & " & myRealRng.Parent.Name & "--" & mycell.value


(I didn't test it.)

atxcomputers wrote:

Hi

The below U/D function matches a string of characters from a given cell
to text on other sheets and then tells me which sheets it is on.

I would like to find out what it has found but cant seem find how to
get that result.

I have highlighted the bit that does the compare and feel it has
something to do with this

Dave Peterson Wrote:


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

I tried changing myRealRng.Parent.Name but just came back with VALUE#

Hopefully Dave Peterson will help me as he did the above function but
if anyone else can help then please do so


Thanks in advance

Regards

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

Dave Peterson Wrote:
This line builds the string to return:

myStr = myStr & " & " & myRealRng.Parent.Name

Maybe you could just change it to:

myStr = myStr & " & " & myRealRng.Parent.Name & "--" & mycell.value

Hi Dave,

Cheers for the reply but id already sorted it.

Great code....Cheers again

Craig


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

Hi Dave,

Ive been playing with this for a few days now and im really happy with
it, except for 1 small thing thats bothering me.

Some of the searches dont give me the correct answer because of the
below:

Sheet1
A1 = 789dinky1977
B2 answer = Toys & 789dinky

A2 = 789dinky1966
B2 answer = Toys & 789dinky

Sheet2 A:A

789dinky
789dinky1977
789dinky1966

It finds the first match and then displays that answer regardless of
whats after

Is there anyway to resolve this ??

Craig

atxcomputers Wrote:
Dave Peterson Wrote:
This line builds the string to return:

myStr = myStr & " & " & myRealRng.Parent.Name

Maybe you could just change it to:

myStr = myStr & " & " & myRealRng.Parent.Name & "--" & mycell.value


--
BSLAUTOMATION
------------------------------------------------------------------------
BSLAUTOMATION's Profile: http://www.excelforum.com/member.php...fo&userid=7611
View this thread: http://www.excelforum.com/showthread...hreadid=400972