advanced excel course london - drodown menus

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » advanced excel course london - Dro-down menus

advanced excel course london - Dro-down menus

resolvedResolved · Low Priority · Version Standard

Seth has attended:
Excel Introduction course
Excel Intermediate course

Dro-down menus

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?

RE: Dro-down menus

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

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

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

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Reconciling a list to correspond with another

May have a list that needs to make but on another sheet one list seems to be out, eg. product list one sheet contains all product and inventory data, while the other contains pricing data. Both need to match with all products but there is more products in one list than the other. To find the disparaging product compare data in the two columns that need to match Make sure that order the same way.
Create another column in the sheet that has the most items and type in the first cell


=Exact(text1,text2) text1 being the cell that you want compared with text2 cell reference. Drag to filldown and your first false will give you for first cells that does not match. Correct insert the row with data in other sheet and continue the process until all the data returns true. Delete the column inserted.

View all Excel hints and tips


Server loaded in 0.09 secs.