Import data from 1 cell on 1 worksheet to othr cell(s) in another

B

BCassedy

Looking to use the inputted/select from drop down data in a cell to populate
other cells using data from another worksheet.
ie.
Worksheet 1 = Main Page
Worksheet 2 = Employee data.

In Wrksht 1, Cell E3, user selects his/her initials from a drop down box
(linked to column on Wrksht 2.

Wrksheet 2 contains data relating to employee.
Column A = Employee's 3 initials (ex. ABC)
Column B = Employee's name (ex. Albert Collin)
Column C = Employee's fone number.

What I would want to do is, when employee goes to Wrksht 1, Cell E3, he/she
has a drop down arrow which provides a listing (from Wrksht 2, column A),
with all employee's initials.

When he/she selects the appropriate 3 initials, the following would happen:
In Wrksht 1, cell E4, his name is filled in (from associated cell in Wrksht
2, col B)
In Wrksht 1, cell F5, his fone number is filled in (from associated cell in
Wrksht 2, col c.

So...
Worksheet 1
cell E3 = manually or drop down box selected 3 initials
cell E4 = auto populated employee name from wrksht 2, cell Bx
cell F5 = auto populated employee fone # from wrksht 2, cell Cx

Ex.
Wrksht 1, cell E3 initials ABC

Wrksht 2, cell A1 = ABC
Wrksht 2, cell B1 = Albert Collin
Wrksht 2, cell C1 = 513-456-7890

When user enters initials (ABC) in Wrksht 1, cell E3, data pulled from
Wrksht 2, cells B1 and C1 and auto populated to Wrksht 1, cells E4 and F5.
 
A

Arvi Laanemets

Hi

Define the range with initials as a named range. Probably a dynamic named
range will do sa best. P.e.

Define a named range Initials with source (I assume A1 on Worksheet2
contains column header, and there are no gaps in employees table):
=OFFSET(Worksheet2!$A$1,1,,COUNTA(Worksheet2!$A:$A)-1,1)

On Worksheet1, define for the cell E3 a data validation list with source
=Initials
You get your drop-down there.

Define another named range, EmplTbl
=OFFSET(Worksheet2!$A$1,1,,COUNTA(Worksheet2!$A:$A)-1,3)

On Worksheet1
E4=VLOOKUP($E$3,EmplTbl,2,0)
F5=VLOOKUP($E$3,EmplTbl,3,0)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top