convenient way to provide "list of values" 5

[electricpete]

Is there a convenient way to provide a "list of values" for data entry. For example choices may be limited to "pump", "motor", "fan" and I would like to provide some flexible prompt.

I tried using data / validation / list but it appears limited to lists that are in the current worksheet. I would like to store a list of acceptable values in other worksheet in same workbook but can't figure out how to do that.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[MintJulep]

VBA form with combo box would do it. May be overkill, and there may be a simpler way.

 

[johnwm]

You can simply insert a Combobox control into your worksheet. Display the VB toolbar (View|toolbars|Visual Basic) and select the combobox tool. Draw it on your worksheet (click&drag). Right click the combo for properties and set ListFillRange property to the cells that contain your list. The output (selected item) will be shown in the cell set as the combobox LinkedCell property

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[bltseattle]

Here is a way that does not require VBA, I use this method frequently.

Find an unused area of the worksheet/book and type in the list of allowable values. It could be on a different sheet. Define a name for the list (the easiest way is to highlight the entire list, go to the name box at the upper left of the row/column headers, type in the desired name and hit enter). (say you use "Allow_val")

Now go to the cell(s) where you want to restrict the input to the list. Go to Data>Validation, select "List", and in the formula bar of the dialogue type "=allow_val"

That should take care of it!

 

[melone]

BLTSEATTLE is correct. You need to use named ranges with Data Validation.

 

[electricpete]

Yes, blt, that's exactly what I wanted. I had tried the same thing before without the equals and it didn't work. I don't quite see the logic for requiring the equals, but at least I can do what I wanted to do.
Thx.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[bltseattle]

Without the equals, it is looking for a comma-separated list of acceptable inputs (value1, value2, value3,...)You could use something like "=sheet1!a1:g1" but descriptive names are much easier to interpret when you are reviewing the sprdsht. Frankly, my sample "allow_val" is not descriptive enough, something like "Category_list" or "Allowable_pipe_sizes" is preferred.

 

[pmover]

to supplement bltseattle solution, the range can also be a dynamic range; that is, automatically expand/contract (add/delete) items from the list without writing code, provided the list is continuous (no row breaks).

for details on dynamic named ranges, see the following website:

http://www.ozgrid.com/Excel/DynamicRanges.htm

btw, i do frequently use dynamic named ranges and data validation lists. oh, dynamic named ranges are also useful for charts.

good luck!
-pmover