Solved

Creating A Drop Down List in Excel 2007

Posted on 2011-02-23
18
264 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)
Comment Utility
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
Comment Utility
Applied to your workbook.

Kevin
ERA-Reg-Template-2011.xlsx
0
 

Expert Comment

by:Tiian6
Comment Utility
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)
Comment Utility
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
Comment Utility
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)
Comment Utility
Teylyn,

Column W.

;-)

Kevin
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
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)
Comment Utility
And you didn't see my post above?

Hmmm...
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Carlynne
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yes, both of your posts were very helpful!!!

Many, many thanks!

carlynne
0
 
LVL 50

Accepted Solution

by:
teylyn earned 167 total points
Comment Utility
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
Comment Utility
Thanks to both of you for your help!
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
Teylyn,

Thanks so much for your explanation!

carlynne
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 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

12 Experts available now in Live!

Get 1:1 Help Now