Excel-drop down menus/corresponding values

C_Mababejive

Hi all,

Im using openoffice but its samey

I want to create a drop down list of items in cell A1

Each drop down menu item would have a value attached to it such that when i select the item from the drop down list, its value appears in B1

All clues welcome !

WaywardDriver

• enter the lookup items in a convenient column say D, with the corresponding values in the adjacent column
• in cell A1 click Data on the menu bar then Data Validation
• select Allow as List and enter Source as =D1.D? replacing ? with the number of items
• in cell B1 enter the formula =VLOOKUP(A1,D1:E?,2,FALSE) replacing ? as before

above works for Excel 2007 but should work for other versions - try help search for Data Validation

C_Mababejive

Thanks,,ive got my named list of 8 items in a drop down menu in cell A40.

I also have a duplicate list of those items in E38>E45 and their corresponding values in F38>F45

What id like to happen is when i select the named item in the menu in cell A40, i would like the corresponding value to appear in C40.

Have had a play with VLOOKUP but run out of time !

Is the above possible? tnx

rmg1

If you mean can you get the corresponding item from column F to appear in cell C40 from the value picked in cell A40 then the answer is yes.
You would need the following formula:-
=vlookup(A40,E38:F45,2,false)

johnmc

If you're using a named range the substitute the name for E38:F45

C_Mababejive

Thanks peeps.. the above more or less worked,just needed minor tweaks for openoffice.

C_Mababejive

PS in the VLOOKUP command line, what does the last bit i,e 2,0 or 2,false do?

I now understand the command,,very useful. I think im noticing in openoffice that the VLOOKUP command line is not being saved when i close the sheet and re-open it,,i might be wrong,,maybe its an openoffice glitch and it will be retained in office..

WaywardDriver

C_Mababejive wrote: »

PS in the VLOOKUP command line, what does the last bit i,e 2,0 or 2,false do?

I now understand the command,,very useful. I think im noticing in openoffice that the VLOOKUP command line is not being saved when i close the sheet and re-open it,,i might be wrong,,maybe its an openoffice glitch and it will be retained in office..

In vlookup(A40,E38:F45,2,false) the 2 means to return the value in the 2nd column in the range E:F i.e. column F. The false means that an exact match is required between the value being looked up (A40) and the values in the first column of the lookup range (column E)

C_Mababejive

WaywardDriver wrote: »

In vlookup(A40,E38:F45,2,false) the 2 means to return the value in the 2nd column in the range E:F i.e. column F. The false means that an exact match is required between the value being looked up (A40) and the values in the first column of the lookup range (column E)

Fab..thanks again to all contributors..:beer: