Archive of Mr Excel Message Board


Back to Data in Excel archive index
Back to archive home

cell pull down menu

Posted by Phil on October 10, 2001 8:25 PM
Some time ago I designed a spreadsheet with cells that had a pull down menu with options for users to insert. I am trying to do this again but cannot remember how.

Any assistance would be appreciated.


Data Validation (NT)

Posted by Richard S on October 10, 2001 8:52 PM


Re: Data Validation (NT)

Posted by Steve Martindale on October 12, 2001 1:47 AM
Two variations on a theme.

1. A short list that doesn't change:
Put the list directly into the source box.
2. A longer list, or one that varies:
Put the list onto your worksheet somewhere convenient (you can hide the row or column if you want). This can contain formulas if necessary. I have one that has a formula to calculate Monday's date in B101, then =B101 + 1 in B102, and so on down to B107.

Select the cell(s) you want to have the drop-down.

Choose Data, Validation from the menu. A dialog box will open. Choose list from the Allow: listbox.

In the Source box put:

Method 1 - Monday, Tuesday, Wednesday, etc - or whatever. The commas matter.

Method 2 - =$B$101:$B$107, for example. You can click the colourful box on the right then select the range, if that's easier.

Make sure the in-cell dropdown box is ticked, add your messages and alerts, and click OK.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.