Solved

Creating A Drop Down List in Excel 2007

Posted on 2011-02-23
18
269 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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:Ingeborg Hawighorst
Ingeborg Hawighorst 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:Ingeborg Hawighorst
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:Ingeborg Hawighorst
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
 

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:Ingeborg Hawighorst
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:
Ingeborg Hawighorst 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:Ingeborg Hawighorst
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:Ingeborg Hawighorst
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

840 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