so i have two excel worksheets

worksheet A has 500 or so individual pieces of data, consisting of last name, first name, and some other shit

each is conveniently numbered 1-500

in worksheet B, I need to create a form that will allow me to enter a number 1-500 and spit out the corresponding last name from worksheet A

after googling for a while i can't come up with anything but shitty excel service tutorials

i'm sure this has a really simple implementation, hook me up tww

[Edited on August 11, 2007 at 2:10 PM. Reason : ]

8/11/2007 2:06:45 PM

vlookup

8/11/2007 2:17:37 PM

right

but how do i combine vlookup with a dropdown list to make this easy

basically i have no experience creating dropdown lists in excel

[Edited on August 11, 2007 at 2:24 PM. Reason : ]

8/11/2007 2:23:55 PM

google is your friend on this one.

insert a dropdown box with the designer controls (im running 2007 now, its in the developer tab on the ribbon, cant remember of the top of my head the legacy layout)



[Edited on August 11, 2007 at 2:27 PM. Reason : .]

8/11/2007 2:24:04 PM

select the cell you want the dropdown to appear, then go to Data->Validation. Change Allow to List and make your source A1:A500, or wherever your source data is

then you can use the dropdown cell in your vookup formula.

[Edited on August 11, 2007 at 2:38 PM. Reason : oh, use something more meaningful than 1-500 in your dropdown. last name seems appropriate]

8/11/2007 2:36:56 PM

what ^ said

use a combination of Data > Validation and VLOOKUP(). to do exactly what you wanted, then just do 1-500 in the data validation, then in the next cell do a vlookup of the dropdown value on the number/name table.
But it would make more sense just to put the names directly in the data validation instead of the numbers

8/11/2007 3:02:46 PM

i'd so use access over excel for some shit like that

8/11/2007 3:13:48 PM

having a lookup table or some simple data validation is not really "some shit". On a complexity rating of shit excel can do, it's probably a 3-4 of 10.
Sure, it can be done easily in Access, but probably <5% of people who use Excel also know how to use Access. For a novice or intermediate Excel using, learning lookup tables and data validation is much faster and easier than learning to do the simplest thing in Access, if they've never used it.

8/11/2007 3:17:44 PM

true.

i've just had an assload of training in access and almost none in excel so i obviously prefer it..

8/11/2007 3:35:24 PM

i finally got it working in excel, but it was kind of clumsy

i just dumped the information into a mysql database and wrote a php form to enter data

much easier

8/11/2007 6:35:52 PM

much easier for who? you or the user? is the user interfacing with excel most of the time or a webpage?

8/11/2007 6:38:45 PM

i should be the only user

[Edited on August 11, 2007 at 7:53 PM. Reason : ]

8/11/2007 7:52:50 PM

oh, well in that case, go for the gusto

8/11/2007 8:08:58 PM