Worksheet Lists

[watermt]

I have an Excel 2003 workbook that contains several worksheets showing data
that I want to be able to add to one of the worksheets and use as a staff
roster. The information is imported from an Access 2003 mdb which is updated
on a daily basis.

My Staff Roster worksheet has column headers for Last Name, First Name,
Employee Number, Department, Hire Date, Seniority Date, and so on and so on.
I'm using the List feature to display selection choices in many of the
columns which pull data from one or more of the other worksheets.

When I select an employee number (say in Cell A1) form the EMP_List, I also
would like to pull the employees Last Name (from EMP_List to Cell B1) and
First Name (from EMP_List to Cell C1).

First can this be done and secondly, if so how would I go about this?

Mike

 

[Gord Dibben]

VLOOKUP formulas in B1 and C1 based upon the value in A1 drawing from a
table on EMP_List sheet.

See Debra Dalgleish's site for instructions and samples of VLOOKUP

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben MS Excel MVP

 

[watermt]

Gord,
I tried referencing the Debra Dalgleish site you gave me but it comes up
with Page Cannot Be Displayed". Possible typo error?

Mike

 

[Gord Dibben]

Did you double-click on the URL I posted?

I just now tried it and worked fine.

I did have a problem a couple hours ago getting to Debra's site.


Gord

 

[watermt]

Gord,
Thanks for the info, yes I too had a hard time connecting yesterday to
Debra's site. But this morning it worked just fine. I went through and
watched her video which really helped me crerate the first part of my
VLookup. Now, I'm stuck on modifying the formula to grab two pieces of
information.

here's my current working formula:
=VLOOKUP(C2,EMPLOYEE_Info!TECHNOLOGY_PROD_GLOBAL_EMPLOYEE_v,4,FALSE)

This works to pull the employees last name to the Last Name column (A1), but
I'd also like this formula to retrieve the First Name and place it in the
First Name column (B1). Would I use the AND function for this in the above
formula?

Thanks,
Mike

 

[Glenn]

If you want information in another cell, you need a formula in that cell, not a
modification to this one. I would assume something like this:

=VLOOKUP(C2,EMPLOYEE_Info!TECHNOLOGY_PROD_GLOBAL_EMPLOYEE_v,5,FALSE)

 

[watermt]

Glenn,
I see where I was going wrong. I did try to place a new formula in a cell
(B2) but I did this:

=VLOOKUP(B2,EMPLOYEE_Info!TECHNOLOGY_PROD_GLOBAL_EMPLOYEE_v,5,FALSE)

instead of this:

=VLOOKUP(C2,EMPLOYEE_Info!TECHNOLOGY_PROD_GLOBAL_EMPLOYEE_v,5,FALSE)

My mistake was referencing the B2 cell (which gave me a circular reference)
and that's when I thought I needed to modify the first VLookup formula by
changing from C2 to B2! But, by placing the new formula that you suggested
solved my problem.

Thanks so much,
Mike

 

[Gord Dibben]

Yep.

You got it. Make the copy to B2

Keep the same lookup value cell.......C2.........just change the column
index number.


Gord

 

[watermt]

Gord,
Having a bit of a problem with the above VLOOKUP formula. It works fine for
columns 2, 3, and 4, but not column 5. I've found that I need more data from
the query and it's under column 5. It's a date & Time (i.e., 02/05/2008
14:17) and have my cell format set to Date. Is there something I'm missing
here?

I copied the formula and changed the column to 5 as indicated below:

=VLOOKUP(C2,EMPLOYEE_Info!TECHNOLOGY_PROD_GLOBAL_EMPLOYEE_v,5,FALSE)

Mike