• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I want to create "Search Cell"

hpbaxxter

Member
Hello all.
I have a list of files' names, and I want to create a cell to show me if there is the files's names which I'm looking for or not.
and the result will shows on another cells !
Is it possible ?
 
Yes! One sample:
Your search cell is "B2"
Your result cell is "B3", write there =iferror("row: " & match(b2,"D:D",0),"none")
Your files' names are from Cell D4 to down as many as needed.
>> Write something to Cell "B2" and You will get the result in "B3".
 
Yes! One sample:
Your search cell is "B2"
Your result cell is "B3", write there =iferror("row: " & match(b2,"D:D",0),"none")
Your files' names are from Cell D4 to down as many as needed.
>> Write something to Cell "B2" and You will get the result in "B3".

thanks for the reply, but it didn't work for me !
I want to add 2 things.
1- If If there were many files have a similar name, I want them to be show all.
2- My file's name have a "hyperlink", I want to be able use the "hyperlink" in the result
 
Okay, You think this but You wrote that.
... term 'similar', means if You write a in 'search cell' then all which has a?
I can only close my eyes and try to figure what is in Your mind...
so as Tip 'tells': Upload a Sample File to get a quicker response
maybe then, someone else too, would figure Your own clear image...
 
Sorry I thought I uploaded an example already !
I mean whan I write a in search cell then all names with a as :
a
aa
bac
a a
show in the result.
Thank you for helping me .
 

Attachments

  • Classeur1.xlsx
    8.4 KB · Views: 4
@hpbaxxter
You wrote before that
My file's name have a "hyperlink", I want to be able use the "hyperlink" in the result
Where are those "hyperlinks" in Your sample?
... test my vision
... there are no 'hyperlinks' but maybe works with those too.
 

Attachments

  • Classeur1.xlsb
    14.6 KB · Views: 5
Please check attachment

Regards
Your "search cell" worked for me but when I add the hyperlink to the file's name, the hyperlink doesn't appear on the result.
I uploaded another example with the hyperlink
[/quote]
 

Attachments

  • Classeur2.xlsx
    12.5 KB · Views: 3
@hpbaxxter
You wrote before that
My file's name have a "hyperlink", I want to be able use the "hyperlink" in the result
Where are those "hyperlinks" in Your sample?
... test my vision
... there are no 'hyperlinks' but maybe works with those too.

it worked well, thanks. I didn't add a hyperlinks because my hyperlinks open files on my pc.
I added a hyperlinks in this new example .
 

Attachments

  • Classeur1.xlsx
    13.5 KB · Views: 6
Your "search cell" worked for me but when I add the hyperlink to the file's name, the hyperlink doesn't appear on the result.
I uploaded another example with the hyperlink

Check revised file, with :

1] Range A6:A14 as "List name"

2] Addition column B6:B14 for "List Link Location".

3] Search criteria, select from C2 dropdown list

4] In "Result" D2, Hyperlink formula copy down :

=HYPERLINK("#'"&IFERROR(INDEX($B$6:$B$14,AGGREGATE(15,6,(ROW($A$6:$A$14)-ROW($A$5))/ISNUMBER(SEARCH($C$2,$A$6:$A$14)),ROWS($1:1))),""),IFERROR(INDEX($A$6:$A$14,AGGREGATE(15,6,(ROW($A$6:$A$14)-ROW($A$5))/ISNUMBER(SEARCH($C$2,$A$6:$A$14)),ROWS($1:1))),""))

Regards
Bosco
 

Attachments

  • Classeur2(1).xlsx
    14.7 KB · Views: 5
Last edited:
@hpbaxxter
Yes, it works with 'hyperlinks' too
even without to do anything else
and
even if You would have over 50 results...
 

Attachments

  • Classeur1.xlsb
    24.9 KB · Views: 3
@hpbaxxter
You wrote before that
My file's name have a "hyperlink", I want to be able use the "hyperlink" in the result
Where are those "hyperlinks" in Your sample?
... test my vision
... there are no 'hyperlinks' but maybe works with those too.

How did you created it ? I tried to change the cells in the "View Code" but it didn't work for me I guess I didn't know how to do it ! hhh
 
@hpbaxxter ... hmm?
How ... I wrote code ... hmm?
You tried to change the cells ... where?
What did You try to do?
... maybe You really didn't know ... hmm
But, what is Your challenge?
I just checked and I found code at once!
 
@hpbaxxter ... hmm?
How ... I wrote code ... hmm?
You tried to change the cells ... where?
What did You try to do?
... maybe You really didn't know ... hmm
But, what is Your challenge?
I just checked and I found code at once!

I changed the cells here (blue color)
but the auto filter didn't work. I need help hhh

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
With ActiveSheet
If .FilterMode Then .ShowAllData
CR = .Range("A3")
LR = .Cells(.Rows.Count, 1).End(xlUp).Row
If LR < 5 Then CR = Empty
If CR = Empty Then
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
End If
CR = "*" & CR & "*"
.Range("A5:A" & LR).AutoFilter Field:=1, Criteria1:=CR
End With

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
@hpbaxxter
Those 'blue's are same as in original codes.
>> Close file
>> ReOpen file
>> Test again
... or ReUpLoad the original code again.
I told what I changed in the original code.
I opened my worksheet which contain my work, and I went to "Developer/ View code. than I pasted the code like this :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$G$8" Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
With ActiveSheet
If .FilterMode Then .ShowAllData
CR = .Range("G8")
LR = .Cells(.Rows.Count, 1).End(xlUp).Row
If LR < 5 Then CR = Empty
If CR = Empty Then
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
End If
CR = "*" & CR & "*"
.Range("K10:K" & LR).AutoFilter Field:=1, Criteria1:=CR
End With

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

But the auto filter didn't always disabled
 
@hpbaxxter
... and Your data ... is still in column "A" as this code needs?
or is it from column "K10" below?
> If Your data value is in different place than in original sample
then ALL NEEDED DIFFERENCES have to change!
>> even 'minor' difference would make ... interesting results
>>> Take care LR's value and as well it's min-value (org 5).
>>> without REAL sample file, I can only make guesses!
 
Back
Top