+ Reply to Thread
Posts 1 to 7 of 7
  1. #1

    Nice But Dim

    DaveB's Avatar
    Join Date
    Oct 2005
    Posts
    14,069
    Thanks (Given)
    1
    Thanks (Received)
    205
    Likes (Given)
    1
    Likes (Received)
    280

    Default Adding a search box to an excel spreadsheet

    I have a work book in Excel 2007 with a sheet for each month jan-dec plus a summary sheet that totals the incidents opened and closed each month and does a nice little picture for the management.

    Each months sheet contains details of specific actions or incidents that took place that month.

    Each action or incident has a unique identifier.

    I want to be able to add a search box to the summary sheet that will replicate the Find funtion without having to click Find & Select, select Find then tick the search entire workbook option each time.

    I want the user to enter the unique ID, hit enter or click a button and have it jump to the cell in the workbook with the matching value.

    Any ideas?
    Careful, you're about to exceed the limits of my medication.

  2. #2

    Double Godlike!

    Spacecadet's Avatar
    Join Date
    Mar 2006
    Location
    Jupiter
    Posts
    12,407
    Thanks (Given)
    13
    Thanks (Received)
    12
    Likes (Given)
    16
    Likes (Received)
    39

    Default

    when ever you're not sure what to do in excel macros, simply record and then use the generated macro:

    recording a search generates the following single line of code:

    Code:
    Cells.Find(What:="66", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
    or this to limit your search to a single range
    Code:
        Columns("A:A").Select
        Selection.Find(What:="77", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
    which could probably be changed to something of the form:
    Code:
        range = Columns("A:A")
        range.Find(What:="77", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
    Last edited by Spacecadet; 7th April 2011 at 11:29.
    Coffee's for closers

  3. #3

    Nice But Dim

    DaveB's Avatar
    Join Date
    Oct 2005
    Posts
    14,069
    Thanks (Given)
    1
    Thanks (Received)
    205
    Likes (Given)
    1
    Likes (Received)
    280

    Default

    Quote Originally Posted by Spacecadet View Post
    when ever you're not sure what to do in excel macros, simply record and then use the generated macro:

    recording a search generates the following single line of code:

    Code:
    Cells.Find(What:="66", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
    or this to limit your search to a single range
    Code:
        Columns("A:A").Select
        Selection.Find(What:="77", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
    which could probably be changed to something of the form:
    Code:
        range = Columns("A:A")
        range.Find(What:="77", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
    Cheers SC, that gives me enough to sort it I think.
    Careful, you're about to exceed the limits of my medication.

  4. #4

    Fingers like lightning


    Join Date
    Oct 2010
    Posts
    623
    Thanks (Given)
    0
    Thanks (Received)
    0
    Likes (Given)
    0
    Likes (Received)
    0

    Default

    Quote Originally Posted by DaveB View Post
    Cheers SC, that gives me enough to sort it I think.
    record macro is your friend, saves trying to learn the excel object tree
    sufficiently advanced stupidity is indistinguishable from malice - Asimov (sort of)

    there is no art in a factory, not even in an art factory - Mixerman

    everyone is stupid some of the time - trad.

  5. #5

    Contractor Among Contractors

    MrRobin's Avatar
    Join Date
    Jun 2007
    Posts
    1,862
    Thanks (Given)
    0
    Thanks (Received)
    0
    Likes (Given)
    0
    Likes (Received)
    0

    Default

    You can do all this in a formula -- i.e. user enters in the ID and it will return the info on the summary page, without the need to go off looking through the other sheets.

    Create a named range, say "MonthSheets" and set the reference as ={"Jan";"Feb";"Mar";...} or whatever your sheet names are called

    Then do some vlookup formulas:

    =VLOOKUP(B2,INDIRECT("'"&INDEX(MonthSheets,MATCH(1 ,--(COUNTIF(INDIRECT("'"&MonthSheets&"'!A:Z"),B2)>0), 0))&"'!A:Z"),5,0)

    where A:Z is your lookup range (with A being the incident ID column), B2 is your ref cell on the summary page to look up and 5 at the end is the column number to return

    Don't forget to enter the formula as an array formula (Ctrl+Shift+Enter)
    It's about time I changed this sig...

  6. #6

    Nice But Dim

    DaveB's Avatar
    Join Date
    Oct 2005
    Posts
    14,069
    Thanks (Given)
    1
    Thanks (Received)
    205
    Likes (Given)
    1
    Likes (Received)
    280

    Default

    Quote Originally Posted by MrRobin View Post
    You can do all this in a formula -- i.e. user enters in the ID and it will return the info on the summary page, without the need to go off looking through the other sheets.

    Create a named range, say "MonthSheets" and set the reference as ={"Jan";"Feb";"Mar";...} or whatever your sheet names are called

    Then do some vlookup formulas:

    =VLOOKUP(B2,INDIRECT("'"&INDEX(MonthSheets,MATCH(1 ,--(COUNTIF(INDIRECT("'"&MonthSheets&"'!A:Z"),B2)>0), 0))&"'!A:Z"),5,0)

    where A:Z is your lookup range (with A being the incident ID column), B2 is your ref cell on the summary page to look up and 5 at the end is the column number to return

    Don't forget to enter the formula as an array formula (Ctrl+Shift+Enter)
    Thanks MR.

    Looks more straight forward but doesn't do what I want , unfortunately. The content of the cell is also a hyper link to a sharepoint documdent that contains the incident details, so I want them to get sent to the cell so they can click thhrough the the incident record itself.
    Careful, you're about to exceed the limits of my medication.

  7. #7

    Contractor Among Contractors

    MrRobin's Avatar
    Join Date
    Jun 2007
    Posts
    1,862
    Thanks (Given)
    0
    Thanks (Received)
    0
    Likes (Given)
    0
    Likes (Received)
    0

    Default

    Quote Originally Posted by DaveB View Post
    Thanks MR.

    Looks more straight forward but doesn't do what I want , unfortunately. The content of the cell is also a hyper link to a sharepoint documdent that contains the incident details, so I want them to get sent to the cell so they can click thhrough the the incident record itself.
    OK fair enough (but you can do that with formulas too )
    It's about time I changed this sig...

+ Reply to Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Content Relevant URLs by vBSEO 3.6.0 ©2011, Crawlability, Inc.