1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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"

Discussion in 'Ask an Excel Question' started by hpbaxxter, Mar 13, 2017.

  1. hpbaxxter

    hpbaxxter Member

    Messages:
    31
    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 ?
  2. vletm

    vletm Well-Known Member

    Messages:
    2,805
    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".
  3. hpbaxxter

    hpbaxxter Member

    Messages:
    31
    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
  4. vletm

    vletm Well-Known Member

    Messages:
    2,805
    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...
  5. hpbaxxter

    hpbaxxter Member

    Messages:
    31
    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 .

    Attached Files:

  6. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    Please check attachment

    Regards

    Attached Files:

  7. vletm

    vletm Well-Known Member

    Messages:
    2,805
    @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.

    Attached Files:

  8. hpbaxxter

    hpbaxxter Member

    Messages:
    31
    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]

    Attached Files:

  9. hpbaxxter

    hpbaxxter Member

    Messages:
    31
    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 .

    Attached Files:

  10. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    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

    Attached Files:

    Last edited: Mar 16, 2017
  11. vletm

    vletm Well-Known Member

    Messages:
    2,805
    @hpbaxxter
    Yes, it works with 'hyperlinks' too
    even without to do anything else
    and
    even if You would have over 50 results...

    Attached Files:

  12. hpbaxxter

    hpbaxxter Member

    Messages:
    31
    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
  13. vletm

    vletm Well-Known Member

    Messages:
    2,805
    @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!
  14. hpbaxxter

    hpbaxxter Member

    Messages:
    31
    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
  15. vletm

    vletm Well-Known Member

    Messages:
    2,805
    @hpbaxxter
    Those 'blue's are same as in original codes.
    >> Close file
    >> ReOpen file
    >> Test again
    ... or ReUpLoad the original code again.
  16. hpbaxxter

    hpbaxxter Member

    Messages:
    31
    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
  17. vletm

    vletm Well-Known Member

    Messages:
    2,805
    @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!
  18. hpbaxxter

    hpbaxxter Member

    Messages:
    31
    visit you private message.

Share This Page