Drop Down List in Excel

Hello,

I need to analyse survey data in excel. I can make a drop down list in 
Excel, via Validation, and get the option to enter one choice. However, one 
question asks respondents to select "all" that apply. 

I do not know how to do this, there are anything from one to six selections. 
The ideal choice would be to be able to selct more than one rather than have 
to enter each choice as a new column.

Any help would be much appreciated 

Thanks
0
Andyroo (6)
6/26/2006 12:19:02 PM
excel.newusers 15348 articles. 2 followers. Follow

8 Replies
995 Views

Similar Articles

[PageSpeed] 25

maybe this would work,
if your dropdown menu is in A1 and the formula is in B1
=IF(A1="a",1,IF(A1="B",2,IF(A1="c",3,IF(A1="d",4,IF(A1="e",5,IF(A1="all",6,""))))))


-- 
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31708
View this thread: http://www.excelforum.com/showthread.php?threadid=555539

0
6/26/2006 12:46:49 PM
Sory, but that went over my head "completely" I dont know ! I have used A1 to 
G1 and the formula / options for validation are somwhere else on the same 
sheet. I just want to be able to select more than one option.

Thanks though ! but im still stuck

"Andyroo" wrote:

> Hello,
> 
> I need to analyse survey data in excel. I can make a drop down list in 
> Excel, via Validation, and get the option to enter one choice. However, one 
> question asks respondents to select "all" that apply. 
> 
> I do not know how to do this, there are anything from one to six selections. 
> The ideal choice would be to be able to selct more than one rather than have 
> to enter each choice as a new column.
> 
> Any help would be much appreciated 
> 
> Thanks
0
Andyroo (6)
6/26/2006 1:00:01 PM
As answered in microsoft.public.excel.worksheet.functions:


You can do this with data validation and programming. There's a sample 
file here:

       http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'Select Multiple Items from Dropdown List'

Andyroo wrote:
> Hello,
> 
> I need to analyse survey data in excel. I can make a drop down list in 
> Excel, via Validation, and get the option to enter one choice. However, one 
> question asks respondents to select "all" that apply. 
> 
> I do not know how to do this, there are anything from one to six selections. 
> The ideal choice would be to be able to selct more than one rather than have 
> to enter each choice as a new column.
> 
> Any help would be much appreciated 
> 
> Thanks


-- 
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
6/26/2006 1:37:30 PM
Debra,

Thanks, I will try that. If I get any problems I will come back

"Debra Dalgleish" wrote:

> As answered in microsoft.public.excel.worksheet.functions:
> 
> 
> You can do this with data validation and programming. There's a sample 
> file here:
> 
>        http://www.contextures.com/excelfiles.html
> 
> Under Data Validation, look for 'Select Multiple Items from Dropdown List'
> 
> Andyroo wrote:
> > Hello,
> > 
> > I need to analyse survey data in excel. I can make a drop down list in 
> > Excel, via Validation, and get the option to enter one choice. However, one 
> > question asks respondents to select "all" that apply. 
> > 
> > I do not know how to do this, there are anything from one to six selections. 
> > The ideal choice would be to be able to selct more than one rather than have 
> > to enter each choice as a new column.
> > 
> > Any help would be much appreciated 
> > 
> > Thanks
> 
> 
> -- 
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
> 
> 
0
Andyroo (6)
6/26/2006 1:54:02 PM
Hi,

I can`t find "Select Multiple Items from drop down List", any other 
suggestions ? I appreciate it

"Debra Dalgleish" wrote:

> As answered in microsoft.public.excel.worksheet.functions:
> 
> 
> You can do this with data validation and programming. There's a sample 
> file here:
> 
>        http://www.contextures.com/excelfiles.html
> 
> Under Data Validation, look for 'Select Multiple Items from Dropdown List'
> 
> Andyroo wrote:
> > Hello,
> > 
> > I need to analyse survey data in excel. I can make a drop down list in 
> > Excel, via Validation, and get the option to enter one choice. However, one 
> > question asks respondents to select "all" that apply. 
> > 
> > I do not know how to do this, there are anything from one to six selections. 
> > The ideal choice would be to be able to selct more than one rather than have 
> > to enter each choice as a new column.
> > 
> > Any help would be much appreciated 
> > 
> > Thanks
> 
> 
> -- 
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
> 
> 
0
Andyroo (6)
6/26/2006 2:16:02 PM
Look at that page again, at that topic (Data Validation) and it's down in the 
list if you scroll down about 1/3 of the way.  You could do [ctrl]+F and 
search for 
Select Multiple Items from Dropdown List 
on that page and it'll take you right to it.  The link to the sample file is 
this:
http://www.contextures.com/DataValMultiSelect.zip

"Andyroo" wrote:

> Hi,
> 
> I can`t find "Select Multiple Items from drop down List", any other 
> suggestions ? I appreciate it
> 
> "Debra Dalgleish" wrote:
> 
> > As answered in microsoft.public.excel.worksheet.functions:
> > 
> > 
> > You can do this with data validation and programming. There's a sample 
> > file here:
> > 
> >        http://www.contextures.com/excelfiles.html
> > 
> > Under Data Validation, look for 'Select Multiple Items from Dropdown List'
> > 
> > Andyroo wrote:
> > > Hello,
> > > 
> > > I need to analyse survey data in excel. I can make a drop down list in 
> > > Excel, via Validation, and get the option to enter one choice. However, one 
> > > question asks respondents to select "all" that apply. 
> > > 
> > > I do not know how to do this, there are anything from one to six selections. 
> > > The ideal choice would be to be able to selct more than one rather than have 
> > > to enter each choice as a new column.
> > > 
> > > Any help would be much appreciated 
> > > 
> > > Thanks
> > 
> > 
> > -- 
> > Debra Dalgleish
> > Contextures
> > http://www.contextures.com/tiptech.html
> > 
> > 
0
JLatham (219)
6/26/2006 11:29:02 PM
I downloaded and ran the macro. I definitely need to learn how to do 
something similar to what shown on "SameCell" sheet. However, I am having 
problems using the VB code to my own sheet. I did copy the VB code from 
DataValMultiSelect.xls (right click on "SameCell" tab -> View Code), then 
paste it to my sheet Book1.xls (right click on "Sheet 3" tab -> View Code). 
What are the next steps? How do I use it on specific cells/columns? How do I 
change the default selection values of One, Two, Three, and so on? Can I have 
different values on what the drop-down displays and what actually selected 
(similar to HTML Form Select)? For example: the dropdown displays the list of 
options as One, Two, Three, etc. Once, one of these options selected, the 
actual value is either B1, B2, B3, etc correspondingly. 



"JLatham" wrote:

> Look at that page again, at that topic (Data Validation) and it's down in the 
> list if you scroll down about 1/3 of the way.  You could do [ctrl]+F and 
> search for 
> Select Multiple Items from Dropdown List 
> on that page and it'll take you right to it.  The link to the sample file is 
> this:
> http://www.contextures.com/DataValMultiSelect.zip
> 
> "Andyroo" wrote:
> 
> > Hi,
> > 
> > I can`t find "Select Multiple Items from drop down List", any other 
> > suggestions ? I appreciate it
> > 
> > "Debra Dalgleish" wrote:
> > 
> > > As answered in microsoft.public.excel.worksheet.functions:
> > > 
> > > 
> > > You can do this with data validation and programming. There's a sample 
> > > file here:
> > > 
> > >        http://www.contextures.com/excelfiles.html
> > > 
> > > Under Data Validation, look for 'Select Multiple Items from Dropdown List'
> > > 
> > > Andyroo wrote:
> > > > Hello,
> > > > 
> > > > I need to analyse survey data in excel. I can make a drop down list in 
> > > > Excel, via Validation, and get the option to enter one choice. However, one 
> > > > question asks respondents to select "all" that apply. 
> > > > 
> > > > I do not know how to do this, there are anything from one to six selections. 
> > > > The ideal choice would be to be able to selct more than one rather than have 
> > > > to enter each choice as a new column.
> > > > 
> > > > Any help would be much appreciated 
> > > > 
> > > > Thanks
> > > 
> > > 
> > > -- 
> > > Debra Dalgleish
> > > Contextures
> > > http://www.contextures.com/tiptech.html
> > > 
> > > 
0
Lamb1 (2)
8/26/2006 1:15:01 PM
To have the code work on a specific column (e.g. column C), you could 
change this line:

   If Intersect(Target, rngDV) Is Nothing Then

to

   If Target.Column = 3 Then

To use a different list of items, you can follow the instructions here:

   http://www.contextures.com/xlDataVal01.html

And to select one item, and show a different value, you could 
incorporate the code in the sample file here:

   http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'


Lamb wrote:
> I downloaded and ran the macro. I definitely need to learn how to do 
> something similar to what shown on "SameCell" sheet. However, I am having 
> problems using the VB code to my own sheet. I did copy the VB code from 
> DataValMultiSelect.xls (right click on "SameCell" tab -> View Code), then 
> paste it to my sheet Book1.xls (right click on "Sheet 3" tab -> View Code). 
> What are the next steps? How do I use it on specific cells/columns? How do I 
> change the default selection values of One, Two, Three, and so on? Can I have 
> different values on what the drop-down displays and what actually selected 
> (similar to HTML Form Select)? For example: the dropdown displays the list of 
> options as One, Two, Three, etc. Once, one of these options selected, the 
> actual value is either B1, B2, B3, etc correspondingly. 
> 
> 
> 
> "JLatham" wrote:
> 
> 
>>Look at that page again, at that topic (Data Validation) and it's down in the 
>>list if you scroll down about 1/3 of the way.  You could do [ctrl]+F and 
>>search for 
>>Select Multiple Items from Dropdown List 
>>on that page and it'll take you right to it.  The link to the sample file is 
>>this:
>>http://www.contextures.com/DataValMultiSelect.zip
>>
>>"Andyroo" wrote:
>>
>>
>>>Hi,
>>>
>>>I can`t find "Select Multiple Items from drop down List", any other 
>>>suggestions ? I appreciate it
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>>As answered in microsoft.public.excel.worksheet.functions:
>>>>
>>>>
>>>>You can do this with data validation and programming. There's a sample 
>>>>file here:
>>>>
>>>>       http://www.contextures.com/excelfiles.html
>>>>
>>>>Under Data Validation, look for 'Select Multiple Items from Dropdown List'
>>>>
>>>>Andyroo wrote:
>>>>
>>>>>Hello,
>>>>>
>>>>>I need to analyse survey data in excel. I can make a drop down list in 
>>>>>Excel, via Validation, and get the option to enter one choice. However, one 
>>>>>question asks respondents to select "all" that apply. 
>>>>>
>>>>>I do not know how to do this, there are anything from one to six selections. 
>>>>>The ideal choice would be to be able to selct more than one rather than have 
>>>>>to enter each choice as a new column.
>>>>>
>>>>>Any help would be much appreciated 
>>>>>
>>>>>Thanks
>>>>
>>>>
>>>>-- 
>>>>Debra Dalgleish
>>>>Contextures
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>>
>>>


-- 
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
8/27/2006 3:39:16 PM
Reply:

Similar Artilces:

Excel on website
Hi, I have an Excel file which opens maximum at my desk, but it opens minimum when I view it on my company's intranet. I use FrontPage 03 and Excel 07. Please advice! Thanks Chi After moving the file to server and linked it to the page I got it! Thanks Chi "Chi" wrote: > Hi, > > I have an Excel file which opens maximum at my desk, but it opens minimum > when I view it on my company's intranet. I use FrontPage 03 and Excel 07. > > Please advice! > > Thanks > Chi > > > > > > Aft...

Drop-down Lists and Worksheet Protection
In Excell 2000 I could set "Validate/Drop down List" for a protected cell and when the worksheet was protected the cell could be "edited" using the drop-down list but not otherwise. Moreover, contents of the cell could be changed but not cleared. Try as I may I can't sem to achieve the same in Excell 2003. Any one have a solution? In Excel 2000, you can select from a data validation list if the source was a worksheet range, but not if the source was a delimited list. In Excel 2003, neither is allowed on a protected sheet. Perhaps you could use a combo box, instea...

Sum
Hi, I just found this "feature" at Excel formulas today. Please, have a look at: http://danielgudang.multiply.com/journal/item/192 (in portuguese, but images show all) Let me explain: some cell C1 = sum (C2:C5) some cell D1 = sum (D2:D5) some cell E1 = sum (E2:E5) A1 = C1 - D1 - E1 B1 = C1 - (D1 + E1) A1 = B1 ??? Oh, not always! Sometimes A1 will be +0, sometimes -0. Really strange! It's a feature of any application that uses IEEE double precision floating point math (e.g., every commercial spreadsheet I know of). It's the result of having finite precision ...

named range, data validation: list non-selected items, and new added items
Greetings all- I'm using Excel2003/Win2000 I have a named range on Sheet2 that has a list of names I have a bunch of non-contiguous cells on Sheet1 that all have data validation that select from that list of names Two questions: (1) I want to use a range of cells at the bottom of Sheet1 to show any names from the list that were /not/ used in any of the data validation cells. Is there a straightforward way to do this without writing a separate formula for each name? I'd like the names to show up in adjacent cells, e.g.: unused: Name 7 Name 18 Name 31 and have that ...

Find a value in list 2 that is not in list 1
I have text values in column A and also in column B, most of which match. I would like to take the values in column B that are not in column A and put those values only in column C. Any help would be greatly appreciated. Maxxwell, Put this formula in column C, and copy down. =IF(ISERROR(MATCH(B2,$A$2:$A$20,0)),B2,"") To get them contiguous, use Autofilter, filter on nonblanks in column C, and copy/paste to somewhere. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "maxxwell2" <maxxwell2@discussions.microsof...

Embedded Excel worksheet in PowerPoint
I'm working with an embedded excel worksheet in powerponit. The doc is sent to me and if I have to edit the worksheet, I find that some cells have missing characters once I open the worksheet to edit it. It appears that characters beyond some length (256?) are being truncated off. How do I resolve this so I don't have this problem again? Thanks, Barb Reinhardt This kind of problem may occur if the material was inserted into the embedded worksheet via copy/paste from an external sheet. A reliable approach is to open the embedded sheet first, and from that sheet do: File ...

Excel 2003 01-27-10
Hi all, hopefully you can help me. In cell B21 the user will always input an email address, i have created a button and now need the button to automatically email the email address in cell B21. I then need it to default and save to a specific location with the file name automatically generated from b12. Please help. :) ...

Purge data based on another Excel file???
I am attempting to purge data from a large list based on data in another document. I have the files I want to remaove in on file and the file I want to remove them from. There should be simple way to do this no? ...

EXCEL 2007
I have a GIS application which uses .dbf files (not sure if they are actually Dbase files). When I need to append data (fields) to the files, I have a big problem in that excel 2007 no longer allows save as / export to .dbf files. If I try bring the data in MS Access and save as .dbf files, I have untold problems with the GIS application... The only way I have been successful with this is to find a user with an older version of excel, insert the data as database columns and then save as .dbf Any suggestions as to how I can get around this with out purchasing a converter? ...

in Excel: How do I change "selected cells" highlight color?
When I select cells in Excel the shading is so light (blue) that I can barely tell which cells are selected from those that are not. Can I change the background color of cells that are selected? Thanks. Assuming Excel 2007 this is a known problem and as yet no patch for it. I don't have Excel 2007 but I have read that playing with your Contrast can help some. Gord Dibben MS Excel MVP On Tue, 10 Jun 2008 12:51:00 -0700, flameretired <flameretired@discussions.microsoft.com> wrote: >When I select cells in Excel the shading is so light (blue) that I can barely >tell whic...

Distribution Lists #8
How can I keep recipient'e email addresses hidden from other recipients after sending an email to the distribution list containing the recipients? >How can I keep recipient'e email addresses hidden from >other recipients after sending an email to the >distribution list containing the recipients? Put the distribution list in the Bcc field, not the To field. -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the name, smiths-aerospace.com is the domain. I don't speak for Smiths, and Smiths doe...

email from excel workbook button does nothing
In my old version of excel(2003) as long as i had outlook open i could email directly from the worksheet. Now i have office 2007 with "windows mail" when i press the button in the "quick access" tool bar it does nothing. I have tried having widows mail open when I do it too but the only way i can send is by making file attachments. seems like the long way around. Is there something I am doing wrong? I have tried to read some of the previous posts and dont understand anything about codes ect. Please help, Mark hi Mark Is Windows Mail your default m...

PLEASE HELP
Hi, In Outlook small business 2007, when I press Tasks or To Do List, It causes outlook to not respond and closes down. Please Help Me. Kara This is the obv. but have you try to repair OL, its under your help Item on your menu bar my 2 cents "Kara Johnson" wrote: > Hi, > In Outlook small business 2007, when I press Tasks or To Do List, It causes > outlook to not respond and closes down. > Please Help Me. ...

Delete duplicate rows from a list in Excel
Hi, Below are instructions on how to "delete duplicate rows from a list in Excel". I learned about this tip from the Microsoft Office Assistant website. However, each time I try step 4, my list is not filtered and no records are hidden. Is there a secret I am missing to make this work? Thanks for your help! -Greg http://office.microsoft.com/en-us/assistance/HA010346261033.aspx Applies to Microsoft Office Excel 2003 Microsoft Excel 2002 A duplicate row (also called a record) in a list is one where all values in the row are an exact match of all the values in another row. To delet...

How to tell Excel to insert cells and shift down from Access
Hi all, I have Access 2003. I am trying to tell Excel to insert cells and shift cells down from Access, with this line of code: oWksh.Cells.Insert Shift:=xlDown But Access does not like that line. It gave me run-time error 1004. I know if has to do with the part Shift:=xlDown Can you suggestion what I can do? Thank you in advance, Ben "Ben" <Ben@NoSpam.com> wrote in message news:eSZuzcDpKHA.1892@TK2MSFTNGP02.phx.gbl... > Hi all, > > I have Access 2003. I am trying to tell Excel to insert cells and shift > cells down from Access,...

Import List of Email Addresses from Access
Is there a way to populate a distribution list in Outlook from a table field in Access? Thanks in advance, Claudette ...

Text Wrap in Excel 2002
I have been having difficultly wrapping text. I merge several cells and wrap text sucessfully. However, if I go into one of the cells and add a couple of sentences and enter, my text that was wrapped perfectly is simply a string of #########'s and I can't view the text correctly by changing the row height or reformatting (even though it is all still there - as I can see in the edit box - when my cursor is on the text box). Any suggestions? Try formatting the cell as General. bdean wrote: > > I have been having difficultly wrapping text. I merge > several cells an...

Excel Version When Posting Please State Your Excel Version!!!
On Jan 8, 6:58=A0pm, "Tyro" <T...@hotmail.com> wrote: > Subject: Excel Version When Posting Please State Your > Excel Version!!! And Please Include Your Questions and Comments In the Body of Your Posting!!! Not just in the subject line, even if it simply duplicates the subject line. Some news readers truncate the subject line. Some news servers do not permit you to reply to articles with empty bodies. Some news servers seem to reject such articles when they come in from the network, or perhaps the originating news server does not push them into the network. That ...

EXCEL Cell Formatting: Custom or Conditional
At work I have Excel 2002 and I have some cells that I import data in from the mainframe. I have a table where there is a column for GENDER (MALE or FEMALE) that I would like to see shown as "Male" or "Female" (mixed case). Is there a way to change all upper case to mixed case using either CUSTOM formatting or CONDITIONAL formatting? I would prefer not using a macro if there is something simpler. I know that =PROPER() produces the correct result, but I don't see how I can use this unless I'm referencing another cell. I would like to be able to just change what fa...

Excel Formulas
I have a spreadsheet showing whether customers have paid their debts or not. Each row represents a customer and details of the debt and the last cell in the row states whether the debt is "PAID" OR "UNPAID". Where the word "PAID" appears in a cell, I want to write a formula that will move that whole row to a different location on my sheet. I eventually want to record this action on a MACRO, so that every time I run a MACRO on my accounts spreadsheet it will take the updated information of customers marked "paid" and put in a seperate spreadsheet. Ple...

how can I change the a,b,c, column headers in excel to names
I've tried and can't figure out how to change the column header from a,b,c, etc. to names for each column. Any tips are appreciated You can only show A,B,C...,IV or numbers: 1,2,3,...,256 But you could put your names in Row 1 and then select A1, then select A2 and click on Window|Freeze panes. espray wrote: > > I've tried and can't figure out how to change the column header from a,b,c, > etc. to names for each column. Any tips are appreciated -- Dave Peterson ...

Copy/Paste Excel Macro
Hello, What I'm trying to do is automate a process using an excel macro. What I need the macro to do is to look for a blank cell in a range of data in Column A, and whenever there is a blank space within that range, paste some specific above information into this row. (I'm formatting an excel file after taking it from another program). I would greatly appreciate any help! Thank you! You could record a macro to filter on blank in coloum A then paste a formula +cell above- this would then fill in your blanks -- Thanks for your help "Justin" wrote: >...

How do I print excel doc from an email
I dont have excell but I received an excell document by email. I am trying to find a way to print the doc. Is this possible? Thanks for any help. Get the free Excel viewer http://www.microsoft.com/downloads/details.aspx?FamilyID=c8378bf4-996c-4569-b547-75edbd03aaf0&displaylang=EN -- Regards Ron de Bruin http://www.rondebruin.nl "kevinb" <kevinb@discussions.microsoft.com> wrote in message news:BB654C4D-8AB3-4D98-89C7-49DA1BDE0C42@microsoft.com... >I dont have excell but I received an excell document by email. I am trying to > find a way to print the doc. Is this ...

Print Preview in Excel
We have office 2000 for small business. we have a peer to peer on 3 systems all sharing one printer. However, the computer with windows xp and office 2000 for small business won't print preview when the network printer, on the other system is off. I've updated office for small business and it still doesn't work. If anyone can assit me with this, it would be most helpful. I am at wits end over this. The other computer with the hp laserjet III is working on windows 98 with office 2000 as well, with not problems. It just seems to be this new computer that has the issue...

Will Excel 2004 run on SnowLeopard OS X 10.6?
Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I'm a SW developer who uses a lot of VBA for Excel programing, for automaation and macros. Besides that, my customers use the spreadsheets I develop in Office for PC's. I know Office 2008 has no VBA for Excel, but I really need it. Can I install Office 2004 in my computer and will it run under Snow Leopard, so that I can keep on using VBA with Excel? Yes -- But do a fresh install. Don't try to 'migrate' Office from an earlier version of the OS if you're upgrading to 10.6 & you will have to use t...