I have this data in sheet 2

Name Addresss Telephone Designation
Vinita Balan International City 213 EMBA
Arunita Paul International City 210 GMBA
Katherine Paras Al Wasl 212 GMBA
I have sheet 1 with this information

Name Addresss Telephone Designation

What I want is when I type the name on sheet1 A2 - B2 to B4 will
automatically show up or a window will pop up will all the data found under
this name like the one in access?

Please help.

Hi,


Try this in B2 and drag right to B4

=VLOOKUP($A2,Sheet2!$A$2:$D$20,COLUMN(B2),FALSE)

change the range A2 - D20 to suit

Mike
"ej" wrote:

I have this data in sheet 2

Name Addresss Telephone Designation
Vinita Balan International City 213 EMBA
Arunita Paul International City 210 GMBA
Katherine Paras Al Wasl 212 GMBA
I have sheet 1 with this information

Name Addresss Telephone Designation

What I want is when I type the name on sheet1 A2 - B2 to B4 will
automatically show up or a window will pop up will all the data found under
this name like the one in access?

Please help.

use the Vlookup fuction

in sheet1, B2 for example

=if(A2="","",Vlookup(A2,Sheet2!A:D,2,False))

in C2
=if(A2="","",Vlookup(A2,Sheet2!A:D,3,False))

in D2

=if(A2="","",Vlookup(A2,Sheet2!A:D,4,False))

--
Regards,
Tom Ogilvy



"ej" wrote:

I have this data in sheet 2

Name Addresss Telephone Designation
Vinita Balan International City 213 EMBA
Arunita Paul International City 210 GMBA
Katherine Paras Al Wasl 212 GMBA
I have sheet 1 with this information

Name Addresss Telephone Designation

What I want is when I type the name on sheet1 A2 - B2 to B4 will
automatically show up or a window will pop up will all the data found under
this name like the one in access?

Please help.

I would add to Mike's comment. You took a great step in putting the data on
a separate sheet. This will allow you to add as many records as necessary.
Now, define a named range using the following formula to point to that data:

Name: tblCustInfo (or whatever)
Refers To: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

This dynamic named range will grow with your data. Now, Mike's formula
becomes :

=VLOOKUP($A2,tblCustInfo,COLUMN(),FALSE)

and it can be copied to each of the columns in your Sheet2 (assuming you use
the same column headings in Sheet2).

HTH,
Pflugs

"ej" wrote:

I have this data in sheet 2

Name Addresss Telephone Designation
Vinita Balan International City 213 EMBA
Arunita Paul International City 210 GMBA
Katherine Paras Al Wasl 212 GMBA
I have sheet 1 with this information

Name Addresss Telephone Designation

What I want is when I type the name on sheet1 A2 - B2 to B4 will
automatically show up or a window will pop up will all the data found under
this name like the one in access?

Please help.