I'll just assume that you want to retieve details involving a name...
Sheet1 houses the data (the lookup table)
Book7 |
---|
|
---|
| A | B | C | D |
---|
1 | NAME | ADDRESS | PHONE | CREDITLIMIT |
---|
2 | murphy | add001 | phone001 | 400.00 |
---|
3 | smith | add002 | phone002 | 400.00 |
---|
4 | johnson | add003 | phone003 | 200.00 |
---|
5 | smith | add004 | phone004 | 400.00 |
---|
6 | henry | add005 | phone005 | 200.00 |
---|
7 | cash | add006 | phone006 | 400.00 |
---|
8 | law | add007 | phone007 | 400.00 |
---|
9 | smith | add008 | phone008 | 400.00 |
---|
10 | rushton | add009 | phone009 | 400.00 |
---|
11 | rolls | add010 | phone010 | 400.00 |
---|
12 | smith | add011 | phone011 | 500.00 |
---|
13 | hartman | add012 | phone012 | 100.00 |
---|
14 | smith | add013 | phone013 | 200.00 |
---|
|
---|
Method 1
A side note. This collapses a number of formula systems of mine for returning multiple values and Just_Jon's (see:
http://216.92.17.166/board2/viewtopic.php?t=73228)...
Select A2:D14.
Go to the Name Box on the Formula Bar, type LTable, and hit enter.
Sheet2a
Select B1.
Activate Data|Validation.
Choose List for Allow.
Enter the following in the Source box:
=INDEX(LTable,0,1)
Click OK.
In A2 enter: Pos [ just a label, abbreviated from Position ]
In B2 enter:
=COUNTIF(INDEX(LTable,0,1),B1)
In A3 enter & copy down:
=IF(COUNTA($A$2:A2)<=$B$2,MATCH($B$1,INDEX(Sheet1!A:A,N(A2)+1):INDEX(Sheet1!A:A,MATCH(BigStr,Sheet1!A:A)),0)+N(A2),"")
In B3 enter, copy across, then down:
=IF(N($A3),INDEX(Sheet1!A:A,$A3),"")
Method 2
Sheet2bBook7 |
---|
|
---|
| A | B | C | D |
---|
1 | NAME | | | |
---|
2 | smith | | | |
---|
3 | NAME | ADDRESS | PHONE | CREDITLIMIT |
---|
4 | smith | add002 | phone002 | 400.00 |
---|
5 | smith | add004 | phone004 | 400.00 |
---|
6 | smith | add008 | phone008 | 400.00 |
---|
7 | smith | add011 | phone011 | 500.00 |
---|
8 | smith | add013 | phone013 | 200.00 |
---|
9 | | | | |
---|
|
---|
In A1 enter: NAME [ a label from LTable ]
Select A2.
Activate Data|Validation.
Choose List for Allow.
Enter the following in the Source box:
-INDEX(LTable,0,1)
Click OK.
Select A1:A2.
Activate Data|Filter|Advanced Filter.
Check Copy to another location.
Enter Sheet1!$A$1:$D$14 in the box for List range.
Enter Sheet2b!$A$1:$A$2 in the box for Criteria range.
Enter Sheet2b!$A$3 in the box for Copy to.
Leave Unique records only unchecked.
Click OK.
The foregoing Advanced Filter is not automatic, although you can record a macro which you can play at will.