Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup info - Really need help please
Good morning. A couple of years ago I saw a formula that would allow you to
look up info in a column (the NAME column below) and return all the data in any row that found a match. So, you could have a separate worksheet that would return all rows that had the name "Smith" or whatever in the Name Column. Can somehow help me figure out that formula? Thanks. Cyndi Jnl Date Trans# Description Account Amount Name 7/1/08 09-000001 Grad Lunch 623812 75.00 Smith 7/2/08 09-000002 Conf Reg Fees 623868 112.00 Smith 7/3/08 09-000003 Telephone 611111 66.00 Jones 7/1/08 09-000004 Staff Lunch 623812 88.00 Jones 8/1/08 09-000005 Cell phone 611150 99.99 Jackson 7/14/08 09-000006 Telephone 611111 108.10 Jackson |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup info - Really need help please
you can use the filter or find tools.is this your means?
"Cyndi513" wrote: Good morning. A couple of years ago I saw a formula that would allow you to look up info in a column (the NAME column below) and return all the data in any row that found a match. So, you could have a separate worksheet that would return all rows that had the name "Smith" or whatever in the Name Column. Can somehow help me figure out that formula? Thanks. Cyndi Jnl Date Trans# Description Account Amount Name 7/1/08 09-000001 Grad Lunch 623812 75.00 Smith 7/2/08 09-000002 Conf Reg Fees 623868 112.00 Smith 7/3/08 09-000003 Telephone 611111 66.00 Jones 7/1/08 09-000004 Staff Lunch 623812 88.00 Jones 8/1/08 09-000005 Cell phone 611150 99.99 Jackson 7/14/08 09-000006 Telephone 611111 108.10 Jackson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup info - Really need help please
I know I can filter, but I want to create a report based on the rows that
apply to a specific name. I'd like to report on a separate worksheets for other users who don't know how to filter. Can you explain what find tools.is? "nader" wrote: you can use the filter or find tools.is this your means? "Cyndi513" wrote: Good morning. A couple of years ago I saw a formula that would allow you to look up info in a column (the NAME column below) and return all the data in any row that found a match. So, you could have a separate worksheet that would return all rows that had the name "Smith" or whatever in the Name Column. Can somehow help me figure out that formula? Thanks. Cyndi Jnl Date Trans# Description Account Amount Name 7/1/08 09-000001 Grad Lunch 623812 75.00 Smith 7/2/08 09-000002 Conf Reg Fees 623868 112.00 Smith 7/3/08 09-000003 Telephone 611111 66.00 Jones 7/1/08 09-000004 Staff Lunch 623812 88.00 Jones 8/1/08 09-000005 Cell phone 611150 99.99 Jackson 7/14/08 09-000006 Telephone 611111 108.10 Jackson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup info - Really need help please
With this mod they can type in all to unfilter. Put a comment in the cell
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$I$1" Then Exit Sub If UCase(Target) = "ALL" Then Range("G1:H1").AutoFilter Field:=2 Else Range("G1:H1").AutoFilter Field:=2, Criteria1:=Target End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... They won't have to know how to use the filter if you set up a worksheet_change event macro to do it for you. Right click sheet tabview codeinsert thismodify to suit. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$I$1" Then Exit Sub Range("G1:H1").AutoFilter Field:=2, Criteria1:=Target End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Cyndi513" wrote in message ... Good morning. A couple of years ago I saw a formula that would allow you to look up info in a column (the NAME column below) and return all the data in any row that found a match. So, you could have a separate worksheet that would return all rows that had the name "Smith" or whatever in the Name Column. Can somehow help me figure out that formula? Thanks. Cyndi Jnl Date Trans# Description Account Amount Name 7/1/08 09-000001 Grad Lunch 623812 75.00 Smith 7/2/08 09-000002 Conf Reg Fees 623868 112.00 Smith 7/3/08 09-000003 Telephone 611111 66.00 Jones 7/1/08 09-000004 Staff Lunch 623812 88.00 Jones 8/1/08 09-000005 Cell phone 611150 99.99 Jackson 7/14/08 09-000006 Telephone 611111 108.10 Jackson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup info - Really need help please
Here's an easy, fast non-array play which drives exactly the results that you
seek Illustrated in this sample: http://www.freefilehosting.net/download/3ijm5 Filter over lines by key col in another sht.xls In Sheet1, Source data is assumed in cols A to F, data from row2 down, with key col = col F (name) Put in say, H2: =IF(Sheet2!$A$1="","",IF(Sheet2!$A$1=F2,ROW(),"")) Copy H2 down to cover the max expected extent of data in key col F Leave H1 blank Then in Sheet2, A dv is created in A1 to select the name, eg: Smith, Jones, etc In B2: =IF(C2="","",ROWS($1:1)) In C2: =IF(ROWS($1:1)COUNT(Sheet1!$H:$H),"",INDEX(Sheet1 !A:A,SMALL(Sheet1!$H:$H,ROWS($1:1)))) Copy C2 across to H2. Select B2:H2, fill down by the same extent as in Sheet1's col H. This returns all the lines for the name selected in A1, neatly bunched at the top. Col B returns a simple row numbering dependent on the # of result lines returned. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Cyndi513" wrote: I know I can filter, but I want to create a report based on the rows that apply to a specific name. I'd like to report on a separate worksheets for other users who don't know how to filter. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup info - Really need help please
G'day Cindi
A great place for help in this area is Ron BeBruins Website, check it out here. http://www.rondebruin.nl/tips.htm This link will take you directly to the area I think will suit your needs Copy records with the same value in a column to a new sheet or workbook http://www.rondebruin.nl/copy5.htm HTH Mark. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup info in one Column and then returning info in other columns | Excel Worksheet Functions | |||
Lookup & Give Specified Info. in Specified column | Excel Worksheet Functions | |||
Lookup does not work -copy info from the above cell | Excel Discussion (Misc queries) | |||
how to skip to next cell to lookup info until found | Excel Worksheet Functions | |||
lookup info in another worksheet | Excel Worksheet Functions |