advanced excel course london - Dro-down menus

The UK's most regular instructor-led training courses.
Training information: advanced excel course london · Excel courses london · Microsoft Excel Training London
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Low Priority · Version Standard

Dro-down menus

Seth has attended:
Excel Introduction course
Excel Intermediate course

by - delegate Seth [3 posts] (2007 Mar 12 Mon, 12:19) Reply

I am wondering if there is a way in Excel to create drop-down menus (similar to HTML Web pages) within columns/rows? I am trying to create a "Form" for people on my team to enter data/info about their assignments, and I would like to limit their data inputs to a select number of options (e.g., sales presentation, print advertisement, press release). I know that "Filters" allow you to temporarily hide rows you do not want displayed, but Filters is based on data subsets already entered into the workbook. Is it possible to create drop-downs also?

Version	Date	Location	Places available	Book	Next place rate:
Excel Intermediate 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2007	2008 Nov 25 Tue	Bloomsbury	0	FULL
2003	2008 Nov 27 Thu	Southwark	3	book now	£210	£215
2007	2008 Nov 28 Fri	Bloomsbury	0	FULL
2003	2008 Dec 2 Tue	Bloomsbury	3	book now	£210	£215
2003	2008 Dec 4 Thu	Bayswater	6	book now	£195	£205
2007	2008 Dec 8 Mon	Bayswater	5	book now	£212	£224
Full Schedule: See all 113 Excel Intermediate course dates. Bookings currently available until 25th November 2009.

RE: Dro-down menus

by - trainer Richard platinum contributer [818 posts] (2007 Mar 12 Mon, 15:53) Reply

Seth,

You can use Data Validation to create limited drop down menus.

1. Select the cells you want the list to apply to.
2. From the Menu Bar, select DATA > VALIDATION.
3. Choose LIST and then type the list, separated by a comma.
4. Complete the interface, and test to see if the list displays correctly.

Let us know how it goes.

Richard

RE: Dro-down menus

by - delegate Seth [3 posts] (2007 Mar 14 Wed, 16:16) Reply

Richard,

This is great! One more quick question. If I am asking my team to enter the total minutes/hours and start/finish dates for each project. Is it possible to create a drop-down menu with dates and or time? The ideal for dates, would be possibly a calendar (like on airline web sites) and time would be in 5 or 10 minute increments...

Any ideas/help would be great. Thanks so much!
Seth

RE: Dro-down menus

by - trainer Rich platinum contributer [744 posts] (2007 Mar 23 Fri, 10:35) Reply

Seth,

There is a built in Microsoft Date Picker, which can also be used to insert the time as well (i'd assume you would have different columns for date and time).

To use this, go to View -> Toolbars -> Control Toolbox. This will bring up the Control Toolbox. The last button is 'more controls...' When you click this, you get a list of all the controls available. Scroll down to Microsoft Date and Time Picker Control 6.0 (SP4), click it, then click somewhere on your work sheet to insert the object. If you can't see it in the list, you have an older version of Excel.

You may need a little bit of VBA knowledge to get the value of each DatePicker box into calculations of your spreadsheet. I'd recommend this example of implementation on the Function-X web site to get you started.

Alternatively, you may be interested in attending our Excel Advanced course or 2 day Excel VBA training course.

See also:
http://pop-up-excel-calendar.billing-invoice-software-office-kit-com.qarchive.org/
http://msdn2.microsoft.com/en-us/library/4za48s1h(VS.80).aspx

Regards, Rich

· Microsoft Excel Training Course
· Microsoft Excel Training - Discover A Hidden Power At Your Fingertips
· Microsoft Excel Training Advanced Courses - Making Data Validation Work For You
· Training Budgets In A Bad Economy
· Microsoft Excel Training - Numerical excellence for all

Excel tip:

Creating custom lists

In Excel if you type in January in a cell, you can then copy this cell to replicate Febraury, MArch, April etc.

This list has come from Tools- options and Custom lists.

Therefore to save time and create your own list you can click on New (in Tools and custom list tab) and type out the lsit that you want copied quickly.

All you have to do is then type in the 1st word and you will be able to copy the rest of the list quickly.

View all Excel hints and tips