asked on

Creating a pull down list in Excel 2003

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
Pull-Down-List.xls

Saqib Husain

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

jppinto

Did you tryed using a Data Validation list?
Capturar.JPG
Pull-Down-List.xls

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jppinto

You can even create "cascading" Validation List:

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_4454-Cascading-Validation-Lists.html

jppinto

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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.

Saqib Husain

+1 teylyn

offthisplanet

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
Pull-Down-List.xls

Rob Henson

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.

Thanks
Rob H

auraorange

ASKER

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