Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menu
In cell A2 I want to put a drop down menu/box. In other words I want to be
able to click on a downward arrow in cell A2 and then have the ability to select from five different options such as I-Phone, Desktop Computer, Laptop Computer, Touch Screen Monitor, and Wireless Internet Connection. If I select any one of these options, (such as Touch Screen Monitor) that selection will appear in cell A2. I guess, I am wondering, is it possible to create a drop down menu in a single cell and if so can you tell me how? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menu
Select the target cell; then go to "Data -- Data validation". Insert a
"List" data validation. Type in the list or select the area containing the list. Click OK and you're ready to go! -- Robert Author of RibbonX: Customizing the Office 2007 Ribbon: FOR THE LATEST TUTORIALS CHECK: http://www.msofficegurus.com/ "Workbook" wrote: In cell A2 I want to put a drop down menu/box. In other words I want to be able to click on a downward arrow in cell A2 and then have the ability to select from five different options such as I-Phone, Desktop Computer, Laptop Computer, Touch Screen Monitor, and Wireless Internet Connection. If I select any one of these options, (such as Touch Screen Monitor) that selection will appear in cell A2. I guess, I am wondering, is it possible to create a drop down menu in a single cell and if so can you tell me how? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menu
Watch this 5 min video:
How to setup a data validation drop down list: http://youtube.com/watch?v=t2OsWJijrOM -- Biff Microsoft Excel MVP "Workbook" wrote in message ... In cell A2 I want to put a drop down menu/box. In other words I want to be able to click on a downward arrow in cell A2 and then have the ability to select from five different options such as I-Phone, Desktop Computer, Laptop Computer, Touch Screen Monitor, and Wireless Internet Connection. If I select any one of these options, (such as Touch Screen Monitor) that selection will appear in cell A2. I guess, I am wondering, is it possible to create a drop down menu in a single cell and if so can you tell me how? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menu
Hi,
An additional comment: it's generally best to put the list into a set of cells and then reference those cells from the Source box. If that list is on a different sheet, then you need to name the list and then enter the name in the Source box, for example =myList What is this generally better? Because it is easy to maintain, just go to the range and correct spelling errors, insert cells for additional choices, delete cells to remove choices. If for example, and not the situation you are talking about, you use the list from more than one cell, you have multiple drop downs, then you only need to edit one list, which is not necessarily the case with entries made manually into the Source box. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Workbook" wrote: In cell A2 I want to put a drop down menu/box. In other words I want to be able to click on a downward arrow in cell A2 and then have the ability to select from five different options such as I-Phone, Desktop Computer, Laptop Computer, Touch Screen Monitor, and Wireless Internet Connection. If I select any one of these options, (such as Touch Screen Monitor) that selection will appear in cell A2. I guess, I am wondering, is it possible to create a drop down menu in a single cell and if so can you tell me how? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menu
Hi Robert,
Much thanks for your help it works great. I noticed the drop menu disappears when I click away from the cell it is located inside of but reappears when I re-click the cell it is located inside of. Is it possible to set up the drop down menu so that it never disappears even when I am clicking in other cells? Thank you for your input, Eddie "Robert Martim, Excel" wrote: Select the target cell; then go to "Data -- Data validation". Insert a "List" data validation. Type in the list or select the area containing the list. Click OK and you're ready to go! -- Robert Author of RibbonX: Customizing the Office 2007 Ribbon: FOR THE LATEST TUTORIALS CHECK: http://www.msofficegurus.com/ "Workbook" wrote: In cell A2 I want to put a drop down menu/box. In other words I want to be able to click on a downward arrow in cell A2 and then have the ability to select from five different options such as I-Phone, Desktop Computer, Laptop Computer, Touch Screen Monitor, and Wireless Internet Connection. If I select any one of these options, (such as Touch Screen Monitor) that selection will appear in cell A2. I guess, I am wondering, is it possible to create a drop down menu in a single cell and if so can you tell me how? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menu
Hi Biff,
Thank you for the link. I learned something that I also needed to know regarding how to set up the list in a different workbook. Thank you again, Ed "T. Valko" wrote: Watch this 5 min video: How to setup a data validation drop down list: http://youtube.com/watch?v=t2OsWJijrOM -- Biff Microsoft Excel MVP "Workbook" wrote in message ... In cell A2 I want to put a drop down menu/box. In other words I want to be able to click on a downward arrow in cell A2 and then have the ability to select from five different options such as I-Phone, Desktop Computer, Laptop Computer, Touch Screen Monitor, and Wireless Internet Connection. If I select any one of these options, (such as Touch Screen Monitor) that selection will appear in cell A2. I guess, I am wondering, is it possible to create a drop down menu in a single cell and if so can you tell me how? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menu
Hi Shane,
Thanks for the tip. You make a good point which I realized after applying the advice I received here. Thank you, Ed "Shane Devenshire" wrote: Hi, An additional comment: it's generally best to put the list into a set of cells and then reference those cells from the Source box. If that list is on a different sheet, then you need to name the list and then enter the name in the Source box, for example =myList What is this generally better? Because it is easy to maintain, just go to the range and correct spelling errors, insert cells for additional choices, delete cells to remove choices. If for example, and not the situation you are talking about, you use the list from more than one cell, you have multiple drop downs, then you only need to edit one list, which is not necessarily the case with entries made manually into the Source box. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Workbook" wrote: In cell A2 I want to put a drop down menu/box. In other words I want to be able to click on a downward arrow in cell A2 and then have the ability to select from five different options such as I-Phone, Desktop Computer, Laptop Computer, Touch Screen Monitor, and Wireless Internet Connection. If I select any one of these options, (such as Touch Screen Monitor) that selection will appear in cell A2. I guess, I am wondering, is it possible to create a drop down menu in a single cell and if so can you tell me how? |
#8
|
|||
|
|||
Please follow following steps
1.Click to sheet2 and enter your data in Column A as A I-Phone Desktop Computer Laptop Computer Touch Screen Monitor Wireless Internet Connection 2.Now select all the data you have entered in sheet2 Column A 3.Now name it as Devices by typing ‘Devices’ on the name bar to the left of formula bar 4.Move to sheet1 5.Select cell A2 6.Click DataValidation from the toolbar 7.Click Setting tab from the data validation window 8.Select List from Allow drop down list 9.In the source box type following =Devices 10.Click OK Now You are done! Click the button that appears next to cell A2 and check it Have a nice time…… Chris ------ Convert your Excel spreadsheet into an online calculator. http://www.spreadsheetconverter.com |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menu
Hi Chris,
Thanks for breaking it out step by step. Ed "Chris Bode" wrote: Please follow following steps 1.Click to sheet2 and enter your data in Column A as A I-Phone Desktop Computer Laptop Computer Touch Screen Monitor Wireless Internet Connection 2.Now select all the data you have entered in sheet2 Column A 3.Now name it as Devices by typing €˜Devices on the name bar to the left of formula bar 4.Move to sheet1 5.Select cell A2 6.Click DataValidation from the toolbar 7.Click Setting tab from the data validation window 8.Select List from Allow drop down list 9.In the source box type following =Devices 10.Click OK Now You are done! Click the button that appears next to cell A2 and check it Have a nice time€¦€¦ Chris ------ Convert your Excel spreadsheet into an online calculator. http://www.spreadsheetconverter.com -- Chris Bode |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menu
You would have to use a different type of drop down. You could use a Forms
toolbar combo box. The drop arrow is always visible with this type of drop down however, it woks differently from a data validation drop down list. A DV drop down automatically sizes to fit a cell. With a combo box, you have to "draw" it to the size and shape you want it. Here are some notes I wrote about the differences: Combo box vs data validation drop down list differences The main difference between a combo box and a data validation drop down list is that a combo box does not occupy a cell. A combo box "floats" above the worksheet. A data validation drop down list does occupy a cell. So, when you make a selection from a data validation drop down list that selection is entered as the value of the cell. When you make a selection from a combo box (there are 2 types of combo boxes) that selected value is not entered into any cell automatically. You have to "format" the combo box to enter the selection in a cell and the cell can be any cell of your choice. The cell that will hold the selection is called the linked cell. In other words, this cell is linked to this combo box. Now comes the fun part! As I said, there are 2 types of combo boxes, a Forms Toolbar combo box and a Control Toolbox combo box. Each of these handles the linked cell a different way. A Forms combo box will return the index number of the selection to the linked cell. A Control combo box will return the actual selection to the linked cell. For example: Tom Bill Sue Lisa Let's say you select Sue from the combo box. If it's a Forms cb, then the linked cell will return the index number 3 because Sue is the 3rd item in the cb. A Control cb will return the actual selection Sue to the linked cell. So, if you need a formula that uses the selection from the cb you have to reference the linked cell. If it's a Control cb then the reference is pretty straightforward. If it's a Forms cb then you have to "translate" the selection index number to the actual selection. In other words, you have to somehow make 3 = Sue. The easiest way to do this is to use an INDEX function. You would index the source of the cb and use the linked cell to tell INDEX which value you want. Suppose the list of names above is in the range A1:A4 and has the defined name of Names. The source of the cb would be Names. Let's assume the linked cell is B1. To do your lookup: =VLOOKUP(A2,sheet2!A1:E22,COLUMNS(A:B),0) If it's Control cb: =VLOOKUP(B1,sheet2!A1:E22,COLUMNS(A:B),0) If it's Forms cb: =VLOOKUP(INDEX(Names,B1),sheet2!A1:E22,COLUMNS(A:B ),0) Here's another "neat" thing about combo boxes. Since they "float" above the worksheet you can "hide" stuff under them. This is the perfect location for the lnked cell. -- Biff Microsoft Excel MVP "Workbook" wrote in message ... Hi Robert, Much thanks for your help it works great. I noticed the drop menu disappears when I click away from the cell it is located inside of but reappears when I re-click the cell it is located inside of. Is it possible to set up the drop down menu so that it never disappears even when I am clicking in other cells? Thank you for your input, Eddie "Robert Martim, Excel" wrote: Select the target cell; then go to "Data -- Data validation". Insert a "List" data validation. Type in the list or select the area containing the list. Click OK and you're ready to go! -- Robert Author of RibbonX: Customizing the Office 2007 Ribbon: FOR THE LATEST TUTORIALS CHECK: http://www.msofficegurus.com/ "Workbook" wrote: In cell A2 I want to put a drop down menu/box. In other words I want to be able to click on a downward arrow in cell A2 and then have the ability to select from five different options such as I-Phone, Desktop Computer, Laptop Computer, Touch Screen Monitor, and Wireless Internet Connection. If I select any one of these options, (such as Touch Screen Monitor) that selection will appear in cell A2. I guess, I am wondering, is it possible to create a drop down menu in a single cell and if so can you tell me how? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menu
Wow this is great! I never knew about combo boxes. Something I am going to
have to consider trying out. Thank you very much! "T. Valko" wrote: You would have to use a different type of drop down. You could use a Forms toolbar combo box. The drop arrow is always visible with this type of drop down however, it woks differently from a data validation drop down list. A DV drop down automatically sizes to fit a cell. With a combo box, you have to "draw" it to the size and shape you want it. Here are some notes I wrote about the differences: Combo box vs data validation drop down list differences The main difference between a combo box and a data validation drop down list is that a combo box does not occupy a cell. A combo box "floats" above the worksheet. A data validation drop down list does occupy a cell. So, when you make a selection from a data validation drop down list that selection is entered as the value of the cell. When you make a selection from a combo box (there are 2 types of combo boxes) that selected value is not entered into any cell automatically. You have to "format" the combo box to enter the selection in a cell and the cell can be any cell of your choice. The cell that will hold the selection is called the linked cell. In other words, this cell is linked to this combo box. Now comes the fun part! As I said, there are 2 types of combo boxes, a Forms Toolbar combo box and a Control Toolbox combo box. Each of these handles the linked cell a different way. A Forms combo box will return the index number of the selection to the linked cell. A Control combo box will return the actual selection to the linked cell. For example: Tom Bill Sue Lisa Let's say you select Sue from the combo box. If it's a Forms cb, then the linked cell will return the index number 3 because Sue is the 3rd item in the cb. A Control cb will return the actual selection Sue to the linked cell. So, if you need a formula that uses the selection from the cb you have to reference the linked cell. If it's a Control cb then the reference is pretty straightforward. If it's a Forms cb then you have to "translate" the selection index number to the actual selection. In other words, you have to somehow make 3 = Sue. The easiest way to do this is to use an INDEX function. You would index the source of the cb and use the linked cell to tell INDEX which value you want. Suppose the list of names above is in the range A1:A4 and has the defined name of Names. The source of the cb would be Names. Let's assume the linked cell is B1. To do your lookup: =VLOOKUP(A2,sheet2!A1:E22,COLUMNS(A:B),0) If it's Control cb: =VLOOKUP(B1,sheet2!A1:E22,COLUMNS(A:B),0) If it's Forms cb: =VLOOKUP(INDEX(Names,B1),sheet2!A1:E22,COLUMNS(A:B ),0) Here's another "neat" thing about combo boxes. Since they "float" above the worksheet you can "hide" stuff under them. This is the perfect location for the lnked cell. -- Biff Microsoft Excel MVP "Workbook" wrote in message ... Hi Robert, Much thanks for your help it works great. I noticed the drop menu disappears when I click away from the cell it is located inside of but reappears when I re-click the cell it is located inside of. Is it possible to set up the drop down menu so that it never disappears even when I am clicking in other cells? Thank you for your input, Eddie "Robert Martim, Excel" wrote: Select the target cell; then go to "Data -- Data validation". Insert a "List" data validation. Type in the list or select the area containing the list. Click OK and you're ready to go! -- Robert Author of RibbonX: Customizing the Office 2007 Ribbon: FOR THE LATEST TUTORIALS CHECK: http://www.msofficegurus.com/ "Workbook" wrote: In cell A2 I want to put a drop down menu/box. In other words I want to be able to click on a downward arrow in cell A2 and then have the ability to select from five different options such as I-Phone, Desktop Computer, Laptop Computer, Touch Screen Monitor, and Wireless Internet Connection. If I select any one of these options, (such as Touch Screen Monitor) that selection will appear in cell A2. I guess, I am wondering, is it possible to create a drop down menu in a single cell and if so can you tell me how? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menu
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Workbook" wrote in message ... Wow this is great! I never knew about combo boxes. Something I am going to have to consider trying out. Thank you very much! "T. Valko" wrote: You would have to use a different type of drop down. You could use a Forms toolbar combo box. The drop arrow is always visible with this type of drop down however, it woks differently from a data validation drop down list. A DV drop down automatically sizes to fit a cell. With a combo box, you have to "draw" it to the size and shape you want it. Here are some notes I wrote about the differences: Combo box vs data validation drop down list differences The main difference between a combo box and a data validation drop down list is that a combo box does not occupy a cell. A combo box "floats" above the worksheet. A data validation drop down list does occupy a cell. So, when you make a selection from a data validation drop down list that selection is entered as the value of the cell. When you make a selection from a combo box (there are 2 types of combo boxes) that selected value is not entered into any cell automatically. You have to "format" the combo box to enter the selection in a cell and the cell can be any cell of your choice. The cell that will hold the selection is called the linked cell. In other words, this cell is linked to this combo box. Now comes the fun part! As I said, there are 2 types of combo boxes, a Forms Toolbar combo box and a Control Toolbox combo box. Each of these handles the linked cell a different way. A Forms combo box will return the index number of the selection to the linked cell. A Control combo box will return the actual selection to the linked cell. For example: Tom Bill Sue Lisa Let's say you select Sue from the combo box. If it's a Forms cb, then the linked cell will return the index number 3 because Sue is the 3rd item in the cb. A Control cb will return the actual selection Sue to the linked cell. So, if you need a formula that uses the selection from the cb you have to reference the linked cell. If it's a Control cb then the reference is pretty straightforward. If it's a Forms cb then you have to "translate" the selection index number to the actual selection. In other words, you have to somehow make 3 = Sue. The easiest way to do this is to use an INDEX function. You would index the source of the cb and use the linked cell to tell INDEX which value you want. Suppose the list of names above is in the range A1:A4 and has the defined name of Names. The source of the cb would be Names. Let's assume the linked cell is B1. To do your lookup: =VLOOKUP(A2,sheet2!A1:E22,COLUMNS(A:B),0) If it's Control cb: =VLOOKUP(B1,sheet2!A1:E22,COLUMNS(A:B),0) If it's Forms cb: =VLOOKUP(INDEX(Names,B1),sheet2!A1:E22,COLUMNS(A:B ),0) Here's another "neat" thing about combo boxes. Since they "float" above the worksheet you can "hide" stuff under them. This is the perfect location for the lnked cell. -- Biff Microsoft Excel MVP "Workbook" wrote in message ... Hi Robert, Much thanks for your help it works great. I noticed the drop menu disappears when I click away from the cell it is located inside of but reappears when I re-click the cell it is located inside of. Is it possible to set up the drop down menu so that it never disappears even when I am clicking in other cells? Thank you for your input, Eddie "Robert Martim, Excel" wrote: Select the target cell; then go to "Data -- Data validation". Insert a "List" data validation. Type in the list or select the area containing the list. Click OK and you're ready to go! -- Robert Author of RibbonX: Customizing the Office 2007 Ribbon: FOR THE LATEST TUTORIALS CHECK: http://www.msofficegurus.com/ "Workbook" wrote: In cell A2 I want to put a drop down menu/box. In other words I want to be able to click on a downward arrow in cell A2 and then have the ability to select from five different options such as I-Phone, Desktop Computer, Laptop Computer, Touch Screen Monitor, and Wireless Internet Connection. If I select any one of these options, (such as Touch Screen Monitor) that selection will appear in cell A2. I guess, I am wondering, is it possible to create a drop down menu in a single cell and if so can you tell me how? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down choice needs to lead to another drop down menu | Excel Discussion (Misc queries) | |||
Drop dwn menu. Formula to count selection frm menu in anoth cell? | Excel Worksheet Functions | |||
filter dropdown menu so 2nd drop menu is customized | Excel Worksheet Functions | |||
Drop down menu | Excel Discussion (Misc queries) | |||
Cross-referenced drop-down menu (nested drop-downs?) | Excel Worksheet Functions |