• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Adding a search box to an excel spreadsheet

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    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?
    "Being nice costs nothing and sometimes gets you extra bacon" - Pondlife.

    #2
    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; 7 April 2011, 11:29.
    Coffee's for closers

    Comment


      #3
      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.
      "Being nice costs nothing and sometimes gets you extra bacon" - Pondlife.

      Comment


        #4
        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.

        Comment


          #5
          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...

          Comment


            #6
            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.
            "Being nice costs nothing and sometimes gets you extra bacon" - Pondlife.

            Comment


              #7
              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...

              Comment

              Working...
              X