bit of excel help needed..

skanky

skanky

skanky

Associate
Joined
21 Jul 2005
Posts
1,416
any advice will be greatly appreciated, i'm looking for a dummies guide on how to do the following:

i have a list of name and phone numbers, i want to be able to select via drop down box in A1 the name of a person and then in A2, it will show the corresponding phone number that has been pre-defined.

i've googled about a bit and vlookup looks like it could be what i need, but bit lost on the how-to!
 
i'm not entirely sure if thats the case, i saw a example of what i'm after at work, but the person who made it is long gone.

it was a purchase order form which let you put a cost center in and by selecting a cost center from the predefined list, it added the name of the department to a cell below it.

it seemed to get the list of cost centers from table in a separate worksheet (from the first column) and the cell which has the name of the department had a VLOOKUP code in it which got the information from the 2nd column on same table.

i need that functionality in a new workbook so copy/pasting is out of the question.

i'm not all that good with excel btw! :)
 
Ah, now I get ya.


Yeah a vlookup would work for that.

=VLOOKUP(A1,Sheet2!$A$1:$B$36,2,0)


So if you had your names on the second worksheet in the book that formula would look at what was on Sheet 1 Cell A1 then find it on sheet 2 cell A1 to A36 and return what was in the corresponding row B.

Just remember that with vlookup the information you're trying to look up must always be in the first column of data.
 
yeah thats sounds like the thing i'm after.

how would i go about creating it so that "sheet1, a1" was the drop-down box (where the info from "sheet2, a1-36" would go) so i could then select the info for the above vlookup to display the corresponding data?
 
There are a few steps to go through to achieve what your after, here is a bit of a walk through...

1. Create your list of names and numbers you can place them anywhere, but for this example I'll palce the text "Name" in D1 and in E1 the text "Number" which will be my column headers, then leave an empty row and begin filling in the names and numbers, so you will end up with something looking like this (I've hiden columns A, B & C just so you can see row numbers) -

capturehl4.jpg



2. Once the above is complete go View->toolbars->forms, this shows the forms toolbar. Click on combo box icon in the toolbar, and then draw it in on your sheet, anywhere you fancy, maybe over cell A1?

3. Once drawn, right click on it and select format control. The input range is your column with names in so select cells D3 to D5 (you can extend this later as your list grows). For cell link click any cell for the moment I'm using D9, this will be where an index number is placed and will read either 1,2 or 3 depending upon your choice in the drop down, which isn't much use until its translated into a result.

4. You want the result of the drop down shown in A2, so in cell A2 enter the formulae =INDEX(E3:E5, D9) this then shows the phone number relating to the name picked from your list, dependent upon the index value returned in D9 and looks like this -

capture2mv9.jpg


Thats it done..

When I'm building combo boxes I generally have all the entries on a separate sheet from the combo box to keep things tidy for the user, so all you would see on sheet 1 is the combo box itself and the result.

I have emailed you the example I've built and a development of the above to clean it up a bit, apologies if the above sounds complicated, but if you follow it you should end up with a somewhat cleaner worksheet than using vlookup.

Hope that helps :)
 
Last edited:
example2.xls is exactly the sort of thing i'm after!

if i wanted to add an extra name/number to that list it seems i have to right click on the drop down box then format control and change the input ranges.

is there a way to do it so that excel will automatically read and add to the drop down box anything put in the name/number columns on sheet2 unless its blank?
 
You could probably do it in vba, or you could do it the quick and dirty way by -

1. Extend the range in the control to something huge, say 1000 rows.
2. Format the control and change the number of drop down lines to say 10 (this controls how many items will be displayed when you click on the combo box).
3. Edit the =INDEX(E3:E5, D9) formulae to match your new huge, yet empty list.

If you do that you will still be shown only 10 names at a time and excel will give you a scroll bar beside the list in the combobox, you can then add names and numbers without needing to return to format the control or edit the formulae further.

Like I say its a dirty method but should do what your after.

Also don't forget to sort the names alphabetically so your end user can find them in the list easier.
 
Actually no, the index value would equate to an empty cell so the value 0 would be displayed, you would only get the REf error of you don't extend the index formulae.

If you want to avoid the 0 being displayed you can shift the index formulae to sheet 2 and place an if statement in the cell you want to display the number in, something like
=IF(C1=0,"please pick a valid name",C1) replacing the C1 references with the cell where your index formulae is, and replacing the text between the "" with anything you want, if you want it to be blank just enter "".

I don't see why a user would pick an empty entry in the drop down when they are looking for a person by name?

I'll have a play doing this with a pivot table later today for you :)
 
there wouldnt be any reason why they would pick an empty cell, but if they dont have the choice to pick an empty cell, thats one less way they can screw it up! :)

i'd like to make it as idiot proof as i can.

edit: going back to example2.xls, is there any way of having A1 (drop down box) have some text like "select name here" which gets overwritten when the user selects a name, but also so that the "select name" text isnt selectable as an option after the drop down box is selected?
 
Last edited:
In example 2, you can use the IF statement mentioned above, then as your first entry in the names list place " Please Select a Name" with a space at the start so when you sort alphabetically it will appear at the top of the list and give it the phone number of 0. That way the IF statement will still get a 0 when this is shown and as a result show whatever text you need it to, taking the above example - please pick a valid name.

There may be a neater way of doing that, but it will work sufficiently well.

See how you get on with that, I've updated example 2 and emailed it to you.
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom