Drop Down List:row number

[cbrd]

I have two worksheets, "WS1" and "WS2".
On WS1 there are two columns with the headings "ID" and "NAME" (The values for these start on row 3 and end in row 50).
On WS2 I have a cell, G10.
On WS2 i wish to add a drop down list, and in the list, will be all the values from WS1, Column B3 thru B50('NAMES' column).
When i select a name in the list, I wish for the drop down list to display in cell G10, the ID from column A3 thru A50 on the same row as the choice in the drop down list.

I tried this in excel, but the only thing I could get the drop down list to display in cell G10 was the row number of the name I selected.

I also tried making G11 display the results of the drop down list and putting this formula in G10, and I just get "N/A":
=INDEX(WS1!A3:A50,MATCH(G11,WS1!B3:B50,0))

Any ideas?

[Simon Lloyd]

Hi you need to select the whole list of names and then type in the cell display window (the one to the left of the formula bar) a name for your range, do this for the ID range too, once you have done this click the cell you would like a drop down list of names in and then choose DATA from the menubar, now choose VALIDATION click the drop down arrow on the ALLOW box and select LIST, now type =LIST (where LIST would be your range name but it must be spelt exactly the same as you named it). You should now be able to click the cell you selected for the drop down and a dropdown arrow should appear for you to make your selection, now repeat the excersise for the next list.

Hope this helps,

Regards,

Simon

[cbrd]

Thanks for the response.

Since the List of names is in WS1 and the drop down list box will be in WS2, I dont think I can use "Data Validation"?

[Simon Lloyd]

Sure you can!

Just follow the procedure i gave you but when you choose a cell to add the list validation to choose it on the WS2 page, so your list may be on WS1 column A and your cell that the data appears in can be on WS2!

Give it a go, it worked for me.

Regards,

Simon

[cbrd]

Thanks for the help.
I didnt have the list sorted in ascending order, so it wasnt working right. Now all is well...Thanks again!

[cbrd]

Well I was wrong, it wasnt working correctly.

http://www.ashleylandscaping.com/sample.xls

I posted a sample there (theres a few worksheets in there). Im working on the sheet "Make - Invoice". You will see a drop down list to the middle right. When I selesct a name in the drop down list, it should put a Customer ID # in cell G10. When this happens it fills in the customer address and such, but it doesnt fill in the unpaid info starting on line 16 (it gets this info from sheet "Job History".

Right now I have cell I11 displaying the info from the drop down list and manually typing in G10. I hope I explained it right.