Solved

Creating A Drop Down List in Excel 2007

Posted on 2011-02-23
18
266 Views
Last Modified: 2012-05-11
Hi,

I have a template for data entry. I have two fields that I want to create a drop down list for, including townships and shocks. I have made a list of the all the possible townships and possible shocks. How do I tell Excel to use that list. I have tried selecting the cells that I want the data validation rule to cover > list > I =township or = shocks. But it's not working.

Please see attached template.

thanks,
carlynne
ERA-Reg-Template-2011.xlsx
0
Comment
Question by:Carlynne
  • 7
  • 5
  • 5
  • +1
18 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34958939
When referencing a list from another worksheet the list must be named and the name used for the source.

Kevin
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 83 total points
ID: 34958960
Applied to your workbook.

Kevin
ERA-Reg-Template-2011.xlsx
0
 

Expert Comment

by:Tiian6
ID: 34958962
1. Enter the data into cells
2. Click on the location where the results will be displayed.
3. Click on the Data tab.
4. Click on the Data Validation option from the ribbon to open the menu.
5. Click on the Data Validation in the menu to bring up the dialog box.
6 Click on Settings tab in the dialog box.
7. From the Allow menu choose List.
8. Click on the Source line in the dialog box.
9. Drag select cells in the spreadsheet.
10. Click OK in the dialog box.
11. A down arrow should appear next to cell
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34958968
Tiian6:

The author stated they already tried that.

The list is on another worksheet and thus it must be named and the name used in the validation formula.

Kevin
0
 
LVL 50

Assisted Solution

by:teylyn
teylyn earned 167 total points
ID: 34958971
Hello,

Your named ranges are not defined correctly.

the range name "shocks" refers to cell F22 on the "Shocks" sheet,

Re-define the "shocks" range name to refer to

=Shocks!$A$1:$A$16

instead of F22.

Then you will need to specify data validation for the cells in the ERA_Template sheet. For column D, startiing in  D2, click Data > Data validation > select List in the "Allow" drop down and enter "=Township" (without the quotes) in the "Source" drop down.

As to "Shocks" ... I don't really see where that fits into the data set in the ERA_Template sheet.

Can you explain?

cheers, teylyn
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34958987
Teylyn,

Column W.

;-)

Kevin
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34959075
Ah, found "Shock" in column W.

After you have amended the definition of the named range "Shocks" to

=Shocks!$A$1:$A$16

you can enter a data validation > List with the source

=Shocks

in W2 and copy down.

See attached.

(by the way, it is not a good practice to copy a formula or data validation down for 3000 rows, without any other fields on these rows carrying any value. You may want to read up on the feature of "Excel Tables", which will automatically apply formatting, validation and formulas to new rows that are added to a table, so you do not have to pre-fill thousands of rows with formulas or formatting.

cheers, teylyn
Copy-of-ERA-Reg-Template-2011.xlsx
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34959100
Ta, Kevin. A bit frazzled today, given what's going on in my country of residence.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34959123
And you didn't see my post above?

Hmmm...
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Carlynne
ID: 34959325
Hi Teylyn,

Thanks for your response.

 It's not easy to tell what the data is about from the template alone. There's also a hard copy form that is used for data entry, and that information is entered into the electronic template.  The question on shocks is a question that attempts to assess vulnerability of households.

I was able to redefine the shocks and townships by using the logic that you provided. What does the ! and the $ mean? I can understand the shock values are A1:A16 and the township values are A1:A51. I just don't understand what the ! and $ do. But as I said, it worked! I'm just curious.

Many thanks,
carlynne
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34959350
zorvek, sorry, no, I didn't check out your post and attachment before posting mine.

Happy for you to score the solution, although I feel I did provide some background explanation as to what is required, pointed out the wrong range name reference, and provided a step-by-step, instead of just posting a working file with everything in place.

Different approach, same outcome.

cheers, teylyn
0
 

Author Comment

by:Carlynne
ID: 34959361
Teylyn,

Also, thanks for your comment, "by the way, it is not a good practice to copy a formula or data validation down for 3000 rows, without any other fields on these rows carrying any value. You may want to read up on the feature of "Excel Tables", which will automatically apply formatting, validation and formulas to new rows that are added to a table, so you do not have to pre-fill thousands of rows with formulas or formatting."

I was wondering how one deals with this issue, as I don't know in advance how many rows there will be.  May I just ask do you mean for me to find this in the Excel Help? Can you be more specific where I can find this information?

many thanks,
carlynne
0
 

Author Comment

by:Carlynne
ID: 34959380
Yes, both of your posts were very helpful!!!

Many, many thanks!

carlynne
0
 
LVL 50

Accepted Solution

by:
teylyn earned 167 total points
ID: 34959394
Hello carlynne,

when you define a named range ( for example to  use in a data validation), the named range definition needs to define the sheet name and the range in that sheet. The syntax for that is

=Sheet1!$A$1:$A$10

The ! sign needs to be inserted after the sheet name and the $ signs make sure that the cell and row references are absolute. If all this sounds very confusing, don't despair.

Excel will put these in for you automatically, when you define a range name. Enter the name in the "name" box, then click into the "Refers to" box and then click the sheet and select the cells you want to include in the range.

This will create the correct formula for the named range.

cheers, teylyn
0
 

Author Closing Comment

by:Carlynne
ID: 34959402
Thanks to both of you for your help!
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34959440
Carlynne, thanks for the points.

If you have set up a table with a header row and a few rows of data (let's say 5 rows), just click one cell in this table, then click the Insert ribbon and click Table.

Now the existing table with the header and 5 rows of data are regarded by Excel as a "Table". Excel will add the Autofilter to the headers, so you will see drop down arrows in the column headers, which you can use to sort or filter your data. You may also just ignore them or hit Alt-D-F-F to hide the drop down arrows.

If you enter a value in a cell in the next empty row below the table, Excel will include this row into the "Table". It will automatically apply formatting to the row. It will carry down formulas that calculate values and things like data validation for specific cells. It's very helpful when you want to set up a table for data collection.

In previous versions of Excel, this was called a "List". Excel 2007 and later use the new "Table" structure to help you collect data and use formulas and formatting without having to pre-populate thousands of rows with formats or formulas.

cheers, teylyn
0
 

Author Comment

by:Carlynne
ID: 34959452
Teylyn,

Thanks so much for your explanation!

carlynne
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34959469
Carlynne,

to learn more about Excel 2007 (an later versions) tables, check out

http://office.microsoft.com/en-us/excel-help/manage-information-in-tables-in-excel-2007-HA010219000.aspx

cheers, teylyn
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now