 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2008Does Excel Support Drop-Down Menus to Select Information?
Hello, a very basic question about Excel 2003. Does Excel have a method of letting you use a drop-down menu to select data that's already been entered in a column? I'd like to streamline my selecting a piece of information, and I figured a drop-down menu might be the simplest way. I know Excel has an auto-complete feature, but if a drop-down menu, based on data already entered in a column, can be created, I'd prefer that. If this is possible, without macros or VBA code, or anything like that, what's the procedure? If it's not possible, that's all right. Thank you! Jd > Does Excel have a method of letting you use a drop-down menu to select > data that's already been entered in a column? One thought would be the Autofilter functionality Applied via selecting the col, then clicking Data > Filter > Autofilter  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- In a column, right-click and "Pick from drop down list" may help/ Gord Dibben MS Excel MVP >Hello, a very basic question about Excel 2003. > [quoted text clipped - 12 lines] > >Jd This is exactly what I want, but is there a way to make this a default setting for an entire column? Or will I have to right-click and select this for each and every cell in the column? >In a column, right-click and "Pick from drop down list" may help/ You can use the 'Data Validation' menu command: First you would need to create your list somewhere out of the way of your work area eg cell AA2 has HOUSE, cell AA3 has FLAT, cell AA4 has COTTAGE, cell AA5 has GARAGE, etc Now highlight the column or range of cells in a column that you want the drop-down list to be used in. Then use the Data Validation menu command and in Settings>Allow choose List and this opens up Source - enter your list range here eg AA2:AA5 Click on OK and that should do it for you. > This is exactly what I want, but is there a way to make this a default > setting for an entire column? Or will I have to right-click and > select this for each and every cell in the column? > > >In a column, right-click and "Pick from drop down list" may help/ This worked flawlessly. It's EXACTLY what I want. Thank you. >You can use the 'Data Validation' menu command: >First you would need to create your list somewhere out of the way of your [quoted text clipped - 5 lines] >and this opens up Source - enter your list range here eg AA2:AA5 >Click on OK and that should do it for you. Not quite sure what you are asking. Right-click on any cell you want to enter data into. Pick from list appears to work in non-contguous cells above the list items but not in non-contiguous cells below the list items. Gord >This is exactly what I want, but is there a way to make this a default >setting for an entire column? Or will I have to right-click and >select this for each and every cell in the column? > >>In a column, right-click and "Pick from drop down list" may help/ >Not quite sure what you are asking. Let me elaborate on what I'm doing. It's extremely simple and basic. I've created two databases with Access. One is flat-file, one is relational. They are movie collection databases. Nothing fancy. But I want (for various reasons) a stripped-down list of my movies in spreadsheet form. The only three columns in the spreadsheet will be: Title Genre Location There will be only 20 genre items, i.e. drama, comedy, foreign, mystery, etc. I want some semblance of consistent data entry. For example, I don't want to enter sience fction [sic] by accident. Furthermore, there will be only eight Location items. I do *not* like auto-complete, so I was looking for a way to streamline the Genre and Location selection process. I figured a drop-down menu would be the easiest way. In Access, there is a feature called a Lookup Table. It's a table with pre-entered items, with which you can build a drop-down menu, called a Combo Box. This ensures consistent data entry, and also makes it easier to add new items to the list. You simply add new things to the Lookup Table. Basically, I want the same thing in Excel, if possible. Apparently, it is. However, I want to configure an entire column so that each cell in that column will have a drop-down menu, rather than right-click each and every cell. From what I've read in this thread, something similar to a Lookup table is possible, and having a drop-down menu in each cell of a column is possible. I just have to try it, which I will do soon. That's basically all I want. I hope this clarifies things further. What about Tools>Options>Edit tab, check "Enable AutoComplete for cell values" ? HTH, JP > This is exactly what I want, but is there a way to make this a default > setting for an entire column? Or will I have to right-click and [quoted text clipped - 3 lines] > > - Show quoted text - My first thought, but OP doesn't want to use that feature. Gord >What about > [quoted text clipped - 12 lines] >> >> - Show quoted text - OK, what about a dynamic named range in the same column as you are doing the data entry, then data validation on the entire column? As soon as you typed something in the column, it would be added to the dropdown list. At least, that is my theory, completely untested of course. --JP > My first thought, but OP doesn't want to use that feature. > [quoted text clipped - 5 lines] > > >? Hi JP - problem there is that it won't show you unique entries - it will show your list as everything you have entered in that column, and it could also add unwanted entries into the list. That is why the easiest way would be enter your list of known entries elsewhere. > OK, what about a dynamic named range in the same column as you are > doing the data entry, then data validation on the entire column? As [quoted text clipped - 13 lines] > > > > >? I think what threw me off is the OP said "use a drop-down menu to select data that's already been entered in a column" so I assumed that meant the SAME column as where new data is being entered. Hence the forays into AutoComplete, Extend Formatting, etc. A simple misunderstanding, which you seem to have solved. Thx, JP > Hi JP - problem there is that it won't show you unique entries - it will show > your list as everything you have entered in that column, and it could also [quoted text clipped - 20 lines] > > - Show quoted text - Sometimes I don't articulate very well what I want from software. I either don't know the nomenclature well enough, or...whatever. But I think everyone's on the same wavelength now. The procedure demonstrated in this thread worked precisely as I hoped. Jd >I think what threw me off is the OP said "use a drop-down menu to >select data that's already been entered in a column" so I assumed that >meant the SAME column as where new data is being entered. Hence the >forays into AutoComplete, Extend Formatting, etc. A simple >misunderstanding, which you seem to have solved. In general it's more of a problem with written word... very easy to misunderstand because there's no inflection in these messages, and none of the back and forth in a verbal conversation where these types of things would be more easily understood. Thx, JP > Sometimes I don't articulate very well what I want from software. > I either don't know the nomenclature well enough, or...whatever. [quoted text clipped - 3 lines] > > Jd |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.