Automatic Fill in of manual entry of one cell and Excel does the rest

[Wickidgemini]

Good Morning,
I am new to the forum and looking for help and not sure where to go. If this has been asked previously please do not hesitate to politely direct me to the forum that will help with my issue. I did a search but couldn't find anything.
I know there has to be a way to do this. I have Excel 2007. I am creating a spreadsheet for timesheets. What I want is if I enter the employee ID number in one cell the following cells, first name and last name fill in automatically according to that employee number. Example
Column A Employee ID#, Column B First Name Column C Last Name
A3 I manually enter employee ID #2345. In B3 and C3 the employee's name connected to that employee ID# fills in automatically so I don't have to do it for each employee.

I have over 100 employee's and this time sheet can change daily. I just want the employee's that have time on the sheet each day. I hope this all makes sense.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee ID[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[/TR]
[TR]
[TD]2345 (I manually enter)[/TD]
[TD]John (automatically fills in)[/TD]
[TD]Doe (automatically fills in)[/TD]
[/TR]
</tbody>[/TABLE]

For each cell I already set up a drop down list but that still doesn't enter information when I do that.
Thank You for any help
Donna

 

[VoG]

Try VLOOPUP Excel VLookup Function Examples

 

[Wickidgemini]

Try VLOOPUP Excel VLookup Function Examples

Thank You,
Yep, that for some reason is just not working for me, I keep getting a "check your function", or "I am missing a parenthesis".


Any Other suggestions would be welcomed. I am going to keep playing with this, I don't know if my Excel is not recognizing Vlookup I just don't know but I am thinking I need an alternative.

 

[Wickidgemini]

Thank You,
Yep, that for some reason is just not working for me, I keep getting a "check your function", or "I am missing a parenthesis".


Any Other suggestions would be welcomed. I am going to keep playing with this, I don't know if my Excel does not recognize Vlookup I just don't know but I am thinking I need an alternative.

********YEAH I FIGURED IT OUT I FIGURED IT OUT!!! WHOOHOOO!!!!!!!!!!********
Thank You, Thank You, Thank YOU!!

Sorry I get excited when I learn stuff like this. I am self taught on excel so the more I learn it makes me feel like I accomplished something for the day.
On the suggested website one of the formulas was telling me to use "iferror(vlookup......"
That was where I was having the issues. So I re-read and re-read.. got further help from Microsoft Help (the help sign you have on there) for vlookup. My problem was I was using the iferror, when I took that away it worked!!
Thank You MrExcel!

 

[L. Howard]

Do this to get you going and then adjust to suit your sheet.

A1:A1 has a list of the employee ID#'s
B1:B10 is the first name of employee
C1:C10 is the last name of employee.

F1 is the cell with the drop down of employee ID#'s (select an ID# from the list)

Select G1 and H1, and while still selected, type in this formula =VLOOKUP(F1,A1:C10,{2,3},0) (don't hit enter!)

While STILL selected hold down the ctrl + shift keys and hit enter.
This is Array Enter and you will notice the { } on each end of the formulas in G1 & H1. Excel does this for you, don't type them in when you type in the formula.

Now re-select any ID# in F1 and you will get first and last in G1 & H1.

If you need to alter the formula later, re-select BOTH cells again and while selected, make your changes then Array Enter again.

If you cannot make that work for you, post back, I will give you my email and you can send me an example workbook.

Regards,
Howard

 

[L. Howard]

Wow, I would love to see your reaction to making this VLOOKUP formula work for you.
I looks across ten worksheets and returns ten values when the lookup value is found.<G>

=VLOOKUP($C$2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A11"),C2)>0),0))&"'!A2:K11"),{2,3,4,5,6,7,8,9,10,11},0)

Excel is fun...

Regards,
Howard

 

[Wickidgemini]

Do this to get you going and then adjust to suit your sheet.

A1:A1 has a list of the employee ID#'s
B1:B10 is the first name of employee
C1:C10 is the last name of employee.

F1 is the cell with the drop down of employee ID#'s (select an ID# from the list)

Select G1 and H1, and while still selected, type in this formula =VLOOKUP(F1,A1:C10,{2,3},0) (don't hit enter!)

While STILL selected hold down the ctrl + shift keys and hit enter.
This is Array Enter and you will notice the { } on each end of the formulas in G1 & H1. Excel does this for you, don't type them in when you type in the formula.

Now re-select any ID# in F1 and you will get first and last in G1 & H1.

If you need to alter the formula later, re-select BOTH cells again and while selecting, make your changes then Array Enter again.

If you cannot make that work for you, post back, I will give you my email and you can send me an example workbook.

Regards,
Howard

WOW!!!!!!!
That worked too!!! I love it!!! ray:

 

[Wickidgemini]

Wow, I would love to see your reaction to making this VLOOKUP formula work for you.
I looks across ten worksheets and returns ten values when the lookup value is found.<g>

=VLOOKUP($C$2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A11"),C2)>0),0))&"'!A2:K11"),{2,3,4,5,6,7,8,9,10,11},0)

Excel is fun...

Regards,
Howard

My first thought is-- and I am weird like this, is creating a worksheet from that formula....

</g>

 

[L. Howard]

Here is a link to an example workbook using the mentioned formula AND an additional formula to tell you which sheet the value was found on.

It looks very cluttered, but everything you need to worry - think about is in the first five rows. All the other stuff below row 5 is reference material to show what is being used in the actual working formulas.

https://www.dropbox.com/s/8kackkjqsdl3n6f/Vlookup%20many%20sheets%20many%20values%20Sheet%20name%20Drop%20Box.xlsm

If you have questions you can contact me at

l h kittle @ Comcast . net

Remove the spaces.

Regards,
Howard

 

[_Jage]

@Wickidgemini What is the formula you figured out can you please post it?