Naming Lists

[selkov]

I have a Workbook with 10 sheets.
On sheet one I would like to have a cell(A1) offer me a pull down list
[ like alt down arrow ] only the list needs to be supplied form
worksheet two (a1-a188). Then at cell (b1) another list getting info from sheet 3 rows (b1-b200), etc.

any ideas?

 

[Damon Ostrander]

Hi selkov,

Yes, you can do this, but not via the usual technique of cell Validation with a list. List validation will not work because the range used for the list must be in the same worksheet as the validated cell. In this case you will have to use a dropdown type ComboBox control from the Controls Toolbox. To do this, put a ComboBox control in cell A1. Hold down the Alt key as you size the combobox to the edges of the cell. Assign the ListFillRange property of the ComboBox to "Sheet2!a1:a188" (without the quotes) and the Linked Cell property to "a1". Repeat this process for each dropdown you need.

 

[Greg Truby]

Hi Damon,

I politely disagree. If the validation list is a named range, it can be on another sheet. I just had a longish thread where I did just that. (Heck, I didn't even know you couldn't set it to another page interactively until I read your post and tried it.)

Regards,

 

[Damon Ostrander]

Hi Greg,

Thank you for correcting me. I do not yet have Excel 2002 (still with 2000) and was not aware this restriction had been lifted.

Damon