Creating a list in an excel cell 3

[roningeek]

ok,
this seems like something that should be relatively easy to do but I cannot figure it out. I want to be able to click in a cell in excel and have a list of possible options come up. Once you click on the choice, that value will be placed into the cell. I do not want to create a separate list box. I have seen this done before but cannot figure out how to do so. If anyone can help me I would greatly appreciate it. thanks

mike

 

[JVFriederick]

Try Data Validation, Settings, Allow and select List, then enter your variables separated by a comma.

 

[snyderj]

Select the cell, then choose Data > Validation from the menu. Set Allow to 'List'. In Source enter a comma-separated list, or reference a range of cells (preferably a named range). This range must be on the same sheet. You'll see that there are other values to set as you see fit.

 

[roningeek]

Thanks to both of you. That worked. It was driving me nuts. I knew there had to be a way to do it. Thanks again.

 

[nat101]

BTW, the range does not have to be on the same sheet. As matter of fact (in 2000) I have a range checked/validated from another workBOOK.

 

[snyderj]

I'd like to know how to point to a list range not on the same sheet. I have XL 9.0.3821 SR-1 and can't see how this can be done.

 

[DaleWatson123321]

Hi snyderj,

First create a Range Name for the list's range.

Then select Data Validation. Under "Settings" ..."Allow", choose "List".

Then under "Settings" ..."Source", enter the Range Name, PRECEEDED by an EQUALS sign (=) e.g. =departments ...where departments is your Range Name.

You should find that this works. You should also try the other options ...Input Message, and Error Alert.

Regards, Dale Watson dwatson@bsi.gov.mb.ca

 

[snyderj]

Thanks, Dale. It's interesting that you can use the named range but not a direct reference. Worked within the same workbook across different sheets, but I could not get it to work across different workbooks.