×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

convenient way to provide "list of values"
5

convenient way to provide "list of values"

convenient way to provide "list of values"

(OP)
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.

RE: convenient way to provide "list of values"

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

RE: convenient way to provide "list of values"

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: www.essexsteam.co.uk

RE: convenient way to provide "list of values"

3
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!

RE: convenient way to provide "list of values"

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

RE: convenient way to provide "list of values"

(OP)
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.

RE: convenient way to provide "list of values"

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.

RE: convenient way to provide "list of values"

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close