We help IT Professionals succeed at work.

Creating a pull down list in Excel 2003

auraorange asked
Hi All

I want to create a pull down list in excel 2003.

I have tried to follow examples found via google searches but end up with a filter style pull down list (see sheet 2)

I created a definedlist in sheet 2 thinking I can then import the list to sheet 1

The pull down I want is 1 heading at the top then for the rest of the rows below I can select a different item from the list not the filtered view I have ended up with on sheet 2
Watch Question

Here is one way to do what I have understood

Select the request range on sheet1
Data > Validation
From "Allow" select "List"
In "Source" select the range of sheet2
Then OK

Did you tryed using a Data Validation list?
Most Valuable Expert 2011
Awarded 2010

Click a cell in the list, then convert it back to a range. Click Data > Lists> convert to range. Then select the cells A2 to A9 on Sheet 2. Then use Insert - Names - Define and enter the name "LookupList" or a similar name without the quotes. Alternatively, enter the name in the name box above row 1, to the left of column A.

In Sheet1, click a cell, then go Data > Validation > select List in the drop-down > enter =LookupList (or the name you entered) in the "Refers to" field. Click OK

Now that cell has a drop down from which you can select one of the values specified in Sheet 2.

Copy the cell and paste it to other cells that you want to behave in the same way.

See attached.
Most Valuable Expert 2011
Awarded 2010

ssaqibh and jppinto: In Excel 2003 you cannot refer a data validation range to a different sheet. That is only possible since 2010. You need a range name to define the list source if it resides in a different sheet.
+1 teylyn
offthisplanetNetwork Engineer

see cell b1
is this sort of what you are after

i cant get it to work from sheet to sheet in 2003 only in 2007+

but u could hide your lists in the far right and use formular i have provided
Rob HensonFinance Analyst

Do you mean the quivalent to Right Clicking on a cell and choosing Pick from List to show a list of entries that are currently in the list.

You could replicate this by inserting rows above the data entry area and pasting in the possible entries. These rows can then be hidden and they will still be referred to on the drop down option unless there are blank rows between.

The keystroke equivalent to Right Click > Pick from List is Alt + Down arrow. You could force the list to appear as soon as a cell in the relvant column is selected by coding into the Selection_Change event. Right click on the tab name and select View Code, copy and paste this code in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 1 Then SendKeys ("%{DOWN}")

End Sub

Open in new window

Change the target.Column = 1 to reflect which column is required, Column A = 1, B = 2 etc.

Rob H


Thanks for all feedback, however, this accepted solution enabled me to follow instructions with ease and create lookup lists which is what I was trying to achieve