Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Drop-Down box in Excel Spreadsheet?

Old 04-22-04, 09:55 AM
  #1  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Sep 2000
Location: CT
Posts: 5,177
Drop-Down box in Excel Spreadsheet?

is there any way to incorporate a drop-down box into an excel spreadsheet? (in other words, putting multiple selectable values in one cell, like you do when you filter.) just had someone ask me about it, and she swears she's seen it before..
mkdevo is offline  
Old 04-22-04, 10:16 AM
  #2  
DVD Talk Legend
 
Join Date: Oct 1999
Location: |-|@><0r [email protected]|)
Posts: 17,214
Yes - use this option in Microsoft Excel called "Microsoft Access."

(If you've never used Access, I strongly urge you to try it. It basically works like Excel, except that you have much tighter control over the data entered into any cell. You can format it to be a selection from a set of values in a drop-down list... including a list of entries in another table in the database. Imagine you run an e-commerce site, and have a database with two tables - "Orders" and "Products." You can, very easily, set a column of the "Orders" database to list the entries in the "Product names" column of "Products.")

The only way to do it in Excel, afaik, is a real hack. Put the drop-down list entries in a column, then right-click the cell immediately above those entries and select "Pick from drop-down list." Very annoying.

- David Stein

Last edited by sfsdfd; 04-22-04 at 10:19 AM.
sfsdfd is offline  
Old 04-22-04, 11:57 AM
  #3  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Sep 2000
Location: CT
Posts: 5,177
Originally posted by sfsdfd
Yes - use this option in Microsoft Excel called "Microsoft Access."
heh.. yeah, i told her to use access, but she was sure she'd seen it in excel, and wanted to find out how...

i didn't know about that in excel though. just checked it out.. not ideal, but it's good to know.. thanks..
mkdevo is offline  
Old 04-22-04, 01:11 PM
  #4  
Stealth Moderator
 
namja's Avatar
 
Join Date: Oct 1999
Location: High Definition
Posts: 24,967
Yes, you can do it in Excel. It's under Data -> Validation.

First you make a list, preferably in another worksheet. If your main page is Sheet1, we'll say the list page is Sheet2.
Let's say that you want the drop-down box to have five names. Enter 5 names in Sheet2:
A1 = David
A2 = John
A3 = Bill
A4 = Geoff
A5 = Hillary

Select these 5 cells and name the cells firstnames (in case you don't know how to do this, select 5 cells, then click on the Name Box which is located just above column A, then type in firstnames). Go to Sheet1. Click on the cell where you want the drop-down box to be. Click on Data -> Validation. Under Allow, choose List. For Source, type in =firstnames (make sure you put the equal sign there).

Voila!
namja is offline  
Old 04-23-04, 08:06 AM
  #5  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Sep 2000
Location: CT
Posts: 5,177


excellent! thanks namja! (got your im from last night; wasn't around though.)
mkdevo is offline  
Old 04-23-04, 09:26 AM
  #6  
DVD Talk Legend
 
Join Date: Oct 1999
Location: |-|@><0r [email protected]|)
Posts: 17,214
Ooh - nice one, namja.

- David Stein
sfsdfd is offline  
Old 04-23-04, 01:44 PM
  #7  
DVD Talk Gold Edition
 
Join Date: Jun 1999
Location: Seattle, WA USA
Posts: 2,548
Not sure if this is what you want, but this definitely helps me for my spreadsheets:

you can go to Data --> Sort --> Auto Filter while your headings are highlighted to filter by column.
MitzEclipse is offline  
Old 04-23-04, 01:53 PM
  #8  
Stealth Moderator
 
namja's Avatar
 
Join Date: Oct 1999
Location: High Definition
Posts: 24,967
Well, hope everything worked out okay.

As you probably noticed, you can do other stuff with Data Validation. I used to teach Business Computing at Illinois MBA 4-5 years ago, and this is one of the few cool features that I still remember.

Enjoy.
namja is offline  

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive Advertising Cookie Policy Privacy Statement Terms of Service

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.