Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Question Data Validation Drop down list to Autofill next column

Hi there!

I've been googling for the last 2 weeks trying to find the easiest way to create my worksheet - however i can't find much info. Basically what I want to create is
Column 1 = Names of people
Column 2 = Team Leaders
Column 3 = Department
There won't be an end to this sheet (so 1 sheet per year)

I have created 2 worksheet - 1 is called Log the other is Datas


What I want to happen is use the drop down list of names of people ( which I was able to create - by using data validation (that was easy).

Now the hard bit is, when I pick a name from the drop down list and I want their Team Leader to auto fill column 2 and on column 3 to auto fill their department.

I'm not sure if this is a Vlookup. It just seems so hard to figure out. If you know how to create this work sheet = please let me know. Your feedback would be appreciated.

Please be aware I'm UBER newbie w/ excel
  #2   Report Post  
Senior Member
 
Location: Hyderabad
Posts: 237
Thumbs up

Quote:
Originally Posted by j8zel View Post
Hi there!

I've been googling for the last 2 weeks trying to find the easiest way to create my worksheet - however i can't find much info. Basically what I want to create is
Column 1 = Names of people
Column 2 = Team Leaders
Column 3 = Department
There won't be an end to this sheet (so 1 sheet per year)

I have created 2 worksheet - 1 is called Log the other is Datas


What I want to happen is use the drop down list of names of people ( which I was able to create - by using data validation (that was easy).

Now the hard bit is, when I pick a name from the drop down list and I want their Team Leader to auto fill column 2 and on column 3 to auto fill their department.

I'm not sure if this is a Vlookup. It just seems so hard to figure out. If you know how to create this work sheet = please let me know. Your feedback would be appreciated.

Please be aware I'm UBER newbie w/ excel

yes, you are on the right track. Data validation and vlookup fetches the data as per your requirement.

refer to attached sheet, i made some sample data for you.

all the best
Attached Files
File Type: zip Team details.zip (8.0 KB, 1187 views)
__________________
Thanks
Bala
  #3   Report Post  
Junior Member
 
Posts: 3
Smile

Quote:
Originally Posted by bala_vb View Post
yes, you are on the right track. Data validation and vlookup fetches the data as per your requirement.

refer to attached sheet, i made some sample data for you.

all the best
Thanks for replying back. Your spreadsheet is exactly what I want. I don't know how you did it. Will you be able to show me a step by step guide. I got the drop down list through validation however I don't know how to use Vlookup. Your work is appreciated. Thanks
  #4   Report Post  
Junior Member
 
Posts: 1
Wink

Quote:
Originally Posted by j8zel View Post
Hi there!

I've been googling for the last 2 weeks trying to find the easiest way to create my worksheet - however i can't find much info. Basically what I want to create is
Column 1 = Names of people
Column 2 = Team Leaders
Column 3 = Department
There won't be an end to this sheet (so 1 sheet per year)

I have created 2 worksheet - 1 is called Log the other is Datas


What I want to happen is use the drop down list of names of people ( which I was able to create - by using data validation (that was easy).

Now the hard bit is, when I pick a name from the drop down list and I want their Team Leader to auto fill column 2 and on column 3 to auto fill their department.

I'm not sure if this is a Vlookup. It just seems so hard to figure out. If you know how to create this work sheet = please let me know. Your feedback would be appreciated.

Please be aware I'm UBER newbie w/ excel

Hi,

Say you have raw data in Col E, F & G(8 rows for ex.) in the same sheet or another sheet(depending on your requirement).

Create a drop down list of Name in Cell A1. Select any name from the drop down.

In Cell B1 write this formula =+VLOOKUP($A$1,$E$1:$G$8,2,0). This will fetch the Team leader name for the selected employee.

In Cell C1 write this formula =+VLOOKUP($A$1,$E$1:$G$8,3,0). This will fetch the corresponding department.

Both Team Leader and Department will change depending on the chosen employee in the drop down list.
  #5   Report Post  
Junior Member
 
Posts: 3
Smile

Quote:
Originally Posted by Ranjan2555 View Post
Hi,

Say you have raw data in Col E, F & G(8 rows for ex.) in the same sheet or another sheet(depending on your requirement).

Create a drop down list of Name in Cell A1. Select any name from the drop down.

In Cell B1 write this formula =+VLOOKUP($A$1,$E$1:$G$8,2,0). This will fetch the Team leader name for the selected employee.

In Cell C1 write this formula =+VLOOKUP($A$1,$E$1:$G$8,3,0). This will fetch the corresponding department.

Both Team Leader and Department will change depending on the chosen employee in the drop down list.
Awesome you the man!!! Thanks heaps - just one more question? The sheet won't have an end to it. Meaning there might be thousands of names so I might end up having thousands of rows. Will I have to always input the formula when I use a row? Let me know. Thanks again
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data validation - drop down list Oscar Charts and Charting in Excel 0 February 18th 10 11:28 AM
Data Validation with Drop-down list Ulrik Loves Horses[_2_] Excel Worksheet Functions 2 October 22nd 08 11:08 AM
DROP DOWN LIST DOES NOT APPEAR USING DATA VALIDATION SEAN DI''''ANNO Excel Discussion (Misc queries) 0 November 27th 07 06:38 PM
Drop down list in Data Validation Big Rick Excel Discussion (Misc queries) 3 October 4th 05 04:49 PM
Data Validation using List (But needs unique list in drop down lis Tan New Users to Excel 1 July 8th 05 03:32 PM


All times are GMT +1. The time now is 12:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"