Jump to content
Sign in to follow this  
gamble

Excel Help (Auto Populate)

Recommended Posts

Im trying to make a spreadsheet so that when I select a name from the drop down list, it will auto populate two more fields with primary phone and secondary phone for that contact. So, for example I select Jimbo from the drop down, both his phone numbers will appear in two separate fields adjacent to the name. It cant be that hard but having trouble figuring it out. I have all the data in one worksheet that Id like to link to the first worksheet within the same spreadsheet.

 

Thanks!!!!

Share this post


Link to post
Share on other sites

Something like:

 

 

=IF(E2=jimbo,A6,IF(E2=fred,A7,IF(E2=johnny,A8)))

 

 

the cells: a6, a7, a8 will have each respective name's details.

 

 

 

I only had to do something like this a few weeks ago.

 

That's just to get you started.

Share this post


Link to post
Share on other sites

You'd be better off using a vlookup.

 

So say you have your data table on sheet2 and you select the name from a drop down list in cell A1 on sheet1. Cell A2 would have a vlookup formula like this...

 

=VLOOKUP(A1,Sheet2!A1:C3,2,FALSE)

 

... and cell A3 would be the same with a 3 before the FALSE instead of a 2.

 

This looks for the name of the person in column A of sheet 2 and returns the data from columns B and C of sheet 2.

Share this post


Link to post
Share on other sites

DiscoInferno is correct, personally I use index and match formula's but its the same as using a vlookup for your perposes.

 

however my advise would be to use names ranges as its easier to track and change if your data set is going to be updated. (The example below is for any version of office that uses the ribbon)

 

A	B	C
1			
2			
3	Name	Phone Number	Mobile
4	Andrew	10001	0408
5	Bob	10002	0409
6	Claire	10003	0410
7	David	10004	0411
8	Edward	10005	0412
9	Frank	10006	0413

In this example, select cells a4:a9 on the formulas tab click define name call it something simple like name_list

Select cells a4:c9 define this as phone_lookup

 

If you want your drop down list in cell A1, select cell A1 on the data tab click data validation, in the validation criteria select allow list and for the source enter your first named range name_list

If you want the phone number to auto populate in cell B2 then enter the formula =vlookup(A1,phone_lookup,2,false)

If you want the phone number to auto populate in cell B2 then enter the formula =vlookup(A1,phone_lookup,3,false)

 

 

If you want to use index and match I would name each column separately (ie Name_list, Phone_list, Mobile_list)

Then use =index(Phone_list,match(A1,Name_list,0)) and =index(Mobile_list,match(A1,Name_list,0))

 

I find the formulas are easier to read this way and it won't fail if you are rearranging the data table (ie if you insert a new column between A and B or if you swap the order of the columns)

 

Index and match is also good if you are comparing against fields that are not in the first row. (Vlookup has to have the search terms first)

For example if you wanted to lookup a phone number and have excell tell you the name of the person you could just do =index(Name_list,match(A1,Phone_list,0))

If you wanted to do this with vlookup you would have to copy (or move) Column A to Column D.

Share this post


Link to post
Share on other sites

I agree with the named ranges thing. The day I discovered these was a blessing indeed, and makes things much nicer when you decide you want to move the original data around. Dynamic named ranges are even cooler as they grow with your data, rather than having to redefine them every time you add more data.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×