Creating A Drop Down List in Excel 2007

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
CarlynneAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
When referencing a list from another worksheet the list must be named and the name used for the source.

Kevin
0
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Applied to your workbook.

Kevin
ERA-Reg-Template-2011.xlsx
0
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.

 
Tiian6Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
Teylyn,

Column W.

;-)

Kevin
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Ta, Kevin. A bit frazzled today, given what's going on in my country of residence.
0
 
zorvek (Kevin Jones)ConsultantCommented:
And you didn't see my post above?

Hmmm...
0
 
CarlynneAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
CarlynneAuthor Commented:
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
 
CarlynneAuthor Commented:
Yes, both of your posts were very helpful!!!

Many, many thanks!

carlynne
0
 
CarlynneAuthor Commented:
Thanks to both of you for your help!
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
CarlynneAuthor Commented:
Teylyn,

Thanks so much for your explanation!

carlynne
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
All Courses

From novice to tech pro — start learning today.