Adding a search box to an excel spreadsheet

[MrRobin]

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 )

[DaveB]

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.

[MrRobin]

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)

[2BIT]

Cheers SC, that gives me enough to sort it I think.

record macro is your friend, saves trying to learn the excel object tree

[DaveB]

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:

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

    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:

    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.

[Spacecadet]

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:

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

    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:

    range = Columns("A:A")
    range.Find(What:="77", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

[DaveB]

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?