Originally posted by DaveB
View Post
- 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!
Collapse
You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:
- You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
- You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
- If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.
Logging in...
Previously on "Adding a search box to an excel spreadsheet"
Collapse
-
Originally posted by MrRobin View PostYou 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)
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.
Leave a comment:
-
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)
Leave a comment:
-
Originally posted by Spacecadet View Postwhen 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
Code:Columns("A:A").Select Selection.Find(What:="77", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate
Code:range = Columns("A:A") range.Find(What:="77", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate
Leave a comment:
-
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
Code:Columns("A:A").Select Selection.Find(What:="77", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate
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.
Leave a comment:
-
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?Tags: None
- Home
- News & Features
- First Timers
- IR35 / S660 / BN66
- Employee Benefit Trusts
- Agency Workers Regulations
- MSC Legislation
- Limited Companies
- Dividends
- Umbrella Company
- VAT / Flat Rate VAT
- Job News & Guides
- Money News & Guides
- Guide to Contracts
- Successful Contracting
- Contracting Overseas
- Contractor Calculators
- MVL
- Contractor Expenses
Advertisers
Contractor Services
CUK News
- Streamline Your Retirement with iSIPP: A Solution for Contractor Pensions Sep 1 09:13
- Making the most of pension lump sums: overview for contractors Sep 1 08:36
- Umbrella company tribunal cases are opening up; are your wages subject to unlawful deductions, too? Aug 31 08:38
- Contractors, relabelling 'labour' as 'services' to appear 'fully contracted out' won't dupe IR35 inspectors Aug 31 08:30
- How often does HMRC check tax returns? Aug 30 08:27
- Work-life balance as an IT contractor: 5 top tips from a tech recruiter Aug 30 08:20
- Autumn Statement 2023 tipped to prioritise mental health, in a boost for UK workplaces Aug 29 08:33
- Final reminder for contractors to respond to the umbrella consultation (closing today) Aug 29 08:09
- Top 5 most in demand cyber security contract roles Aug 25 08:38
- Changes to the right to request flexible working are incoming, but how will contractors be affected? Aug 24 08:25
Leave a comment: