MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.


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

Data Validation (NT)

Posted by Steve Martindale on October 12, 2001 1:47 AM

Re: Data Validation (NT)

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.