Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dropdowns
My wife's school is mandating that they put test scores in a spreadsheet to
admin can print out the entire sheet. I want to add a worksheet that will use a dropdown to list the student's names from Col A, and then populate the rest of this worksheet with the specifics from the Student's ROW. My question is how to get the values into the dropdown... I assume it will be a validtation using something like VLOOKUP, but once VLOOKUP has validated the drop down, how can I populate the rest of the sheet? Thanks Carl |
#2
|
|||
|
|||
Hi Carl
i would use Data / Validation to get the values into the drop down and then VLOOKUP to populate the other information - here's an answer i wrote to a similar question recently ... the "subject matter" is different but the concepts are the same: --- use a combination of data / validation to create the drop down list and VLOOKUP function to populate the related information assuming that in Sheet2 you have the following ..............A..............B 1......Item.....Value 2.....Item1.....10.00 3.....Item2.....15.00 4.....Item3..... 20.00 now select from A1 to the end of your list (A4, in the above example) and choose from the menu, Insert / Name / Create - ensure Top Row is checked and click okay - you've created a named range called "Item" (or whatever the title in A1 is) now select from A1 to the end of the list for all columns (B4 in the above example) and click inside the name box (left of formula bar) and type MyTable and press enter - we've created a second named range. Now click in the cell in Sheet1 where you want your drop down list to appear and choose Data / Validation from the menu - in the settings tab, choose List from the Allow drop down box and then click inside the white box under this and press the F3 key - this will bring up a list of your range names, choose "Item" (or whatever your first range was called) and click Ok. You will now have a drop down list in this cell. Now click in the cell where you want the related information to appear and type =VLOOKUP(A1,MyTable,2,false) where A1 is the cell reference with your drop down list in it - you can use the F3 key for the MyTable bit too. now choose an item & see the related information appear ... delete the item and you'll get a #NA error - this can be supressed by embedding your VLOOKUP in an IF statement e.g. =IF(A1="","",VLOOKUP(A1,MyTable,2,false)) where A1 is the cell reference with your drop down list in it hope this helps, let us know how you go Cheers JulieD "Carl Hilton" wrote in message ... My wife's school is mandating that they put test scores in a spreadsheet to admin can print out the entire sheet. I want to add a worksheet that will use a dropdown to list the student's names from Col A, and then populate the rest of this worksheet with the specifics from the Student's ROW. My question is how to get the values into the dropdown... I assume it will be a validtation using something like VLOOKUP, but once VLOOKUP has validated the drop down, how can I populate the rest of the sheet? Thanks Carl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting combobox dropdowns to appear on a userform when tabbed to. | Excel Discussion (Misc queries) |