Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retriving specific data?????
I have a worksheet set up as an address book, is there a way to be able to
type in a name in a specific cell, and automatically have that persons information pulled up to the top of the screen. I use this at work, and have over 200 names, which I call regularly, and searching through to find it, then having to hold my finger on the screen while calling is getting old. I do have the names seperated as much as possible, but it is still a big list. If you go to startsearch type in a file, it get's pulled to the top of the search page. This is similar to what I am trying to do. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retriving specific data?????
There are a few ways to do this:
http://www.contextures.com/xlautofilter01.html Or, try this: http://www.contextures.com/xlDataVal13.html Or, try this: http://www.contextures.com/xlautofilter02.html Towards the bottom of each page, there is a sample file to download and modify for your purposes. If none of those do what you want, post back with more information. Good luck, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "wt.pm" wrote: I have a worksheet set up as an address book, is there a way to be able to type in a name in a specific cell, and automatically have that persons information pulled up to the top of the screen. I use this at work, and have over 200 names, which I call regularly, and searching through to find it, then having to hold my finger on the screen while calling is getting old. I do have the names seperated as much as possible, but it is still a big list. If you go to startsearch type in a file, it get's pulled to the top of the search page. This is similar to what I am trying to do. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retriving specific data?????
Thank you for the help, this will work for what I want. However, what I was
hoping for was to be able to type a last name, then have everyone with that last name, to be pulled up. I don't want to copy to another location, neither do I want to have to type a whole formula each time. I would like to set up one "empty" cell, that has a function, filter or what ever attached, so that when I type the name "Smith", all 8 people with that name are shown. As it is, only one person is pulled up. Which is fine, I am happy with this, but the other would work better. "ryguy7272" wrote: There are a few ways to do this: http://www.contextures.com/xlautofilter01.html Or, try this: http://www.contextures.com/xlDataVal13.html Or, try this: http://www.contextures.com/xlautofilter02.html Towards the bottom of each page, there is a sample file to download and modify for your purposes. If none of those do what you want, post back with more information. Good luck, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "wt.pm" wrote: I have a worksheet set up as an address book, is there a way to be able to type in a name in a specific cell, and automatically have that persons information pulled up to the top of the screen. I use this at work, and have over 200 names, which I call regularly, and searching through to find it, then having to hold my finger on the screen while calling is getting old. I do have the names seperated as much as possible, but it is still a big list. If you go to startsearch type in a file, it get's pulled to the top of the search page. This is similar to what I am trying to do. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retriving specific data?????
Ok, right click the sheet that you want to filter on. Clik 'View Code' and
paste this code into the window that opens: Option Explicit '--------------------------------------------------------------------------------------- ' Procedure : Worksheet_Change ' Author : Roger Govier, Technology 4 U ' Date : 09-Mar-2008 ' Purpose :To enable filtering without having to use the dropdown arrows ' :Especially useful in XL2007 where you need to deselect all before making ' :a selection. Also save the need to invoke the Custom dialogue ' :Highlighting of cells with the criteria allows easy view of what selections have been made. ' :The code was inspired by a discussion with Dr Peter Grebenik, Brookes University ' :Oxford, who had used something similar in his work. '--------------------------------------------------------------------------------------- ' Private Sub Worksheet_Change(ByVal Target As Range) Dim rownum As Long, colnum As Long Dim tblname As String, mylist As Object Dim caret As Long, caret2 As Long Dim crit1 As String, crit2 As String, optype As String, marker As String 'Set this next value to the row number above your filter Const testrow = 1 'Change the marker to something other than the caret ^ if required marker = "^" On Error GoTo Worksheet_Change_Error rownum = Target.Row colnum = Target.Column On Error Resume Next If Target.Count 1 Then ActiveSheet.ShowAllData Target.Interior.ColorIndex = -4142 'clear colour from range GoTo cleanup End If If rownum < testrow Then GoTo cleanup crit1 = Target.Value caret = InStr(Target, marker) caret2 = InStr(Target, marker & marker) If caret Then crit1 = Trim(Left(Target.Value, caret - 1)) crit2 = WorksheetFunction.Substitute(Mid(Target.Value, caret + 1), marker, "") optype = xlAnd End If If caret2 Then optype = xlOr End If If Val(Application.Version) < 11 Then GoTo earlyversion Set mylist = ActiveSheet.ListObjects If mylist.Count Then ' A List or Table Object is used tblname = mylist(1).Name If Cells(rownum, colnum).Value = "" Then ' No filter choice mylist(tblname).Range.AutoFilter Field:=colnum GoTo cleanup ElseIf caret Then mylist(tblname).Range.AutoFilter Field:=colnum, _ Criteria1:=crit1, Operator:=optype, Criteria2:=crit2 GoTo cleanup Else mylist(tblname).Range.AutoFilter Field:=colnum, _ Criteria1:=crit1 GoTo cleanup End If ' There is no List object, it is a Range so treat the same as ' earlier versions of Excel End If earlyversion: 'This version of Excel does not support List Objects If Cells(rownum, colnum).Value = "" Then Selection.AutoFilter Field:=colnum ElseIf caret Then Selection.AutoFilter Field:=colnum, _ Criteria1:=crit1, Operator:=optype, Criteria2:=crit2 Else Selection.AutoFilter Field:=colnum, Criteria1:=crit1 End If cleanup: 'keep focus on same cell and set colour index if Selection is made Range(Target.Address).Activate If ActiveCell < "" Then ActiveCell.Interior.ColorIndex = 40 'change to colour of your choice Else ActiveCell.Interior.ColorIndex = -4142 End If On Error GoTo 0 Exit Sub Worksheet_Change_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Worksheet_Change of VBA Document Sheet4" ActiveCell.Interior.ColorIndex = -4142 On Error GoTo 0 End Sub Put your data on Row 3 (and below). HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "wt.pm" wrote: Thank you for the help, this will work for what I want. However, what I was hoping for was to be able to type a last name, then have everyone with that last name, to be pulled up. I don't want to copy to another location, neither do I want to have to type a whole formula each time. I would like to set up one "empty" cell, that has a function, filter or what ever attached, so that when I type the name "Smith", all 8 people with that name are shown. As it is, only one person is pulled up. Which is fine, I am happy with this, but the other would work better. "ryguy7272" wrote: There are a few ways to do this: http://www.contextures.com/xlautofilter01.html Or, try this: http://www.contextures.com/xlDataVal13.html Or, try this: http://www.contextures.com/xlautofilter02.html Towards the bottom of each page, there is a sample file to download and modify for your purposes. If none of those do what you want, post back with more information. Good luck, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "wt.pm" wrote: I have a worksheet set up as an address book, is there a way to be able to type in a name in a specific cell, and automatically have that persons information pulled up to the top of the screen. I use this at work, and have over 200 names, which I call regularly, and searching through to find it, then having to hold my finger on the screen while calling is getting old. I do have the names seperated as much as possible, but it is still a big list. If you go to startsearch type in a file, it get's pulled to the top of the search page. This is similar to what I am trying to do. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import data to specific cells for a specific row | Excel Discussion (Misc queries) | |||
Getting specific data into specific cells? | Excel Discussion (Misc queries) | |||
Retriving Values from a separate sheet of a different file | Excel Worksheet Functions | |||
How do I color specific data series based on location on data she | Charts and Charting in Excel | |||
Retriving a deleted version of Excel | Excel Worksheet Functions |