+ Reply to Thread
Results 1 to 8 of 8

How to limit data on a cell.

  1. #1
    Registered User
    Join Date
    07-21-2009
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2003
    Posts
    4

    How to limit data on a cell.

    Is there any way to limit data options in a certain cell? Sometimes I design worksheets where I know that certain column contains a limited amount of options. Lets imagine the following: Column A is for "Salesperson's name" and I know that I only have four ones. Somewhere else I can make a table with the list of names, but... How do I make all users to just choose one of the names in each cell?
    Sorry if this is something explained before, but I just don't even know how to look for!

    Rgds

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: How to limit data on a cell.

    Hi,
    If you have a list of sales people defined somewhere in the workbook, say on a different sheet, highlight that list and then click Insert - Names - Define. Give the range a name like SalesPeople and click OK.

    Now highlight the cells where you want to have a drop-down for the sales people and select Data - Validation. Select "List" in the Allow drop-down box and in the Source box put

    =SalesPeople

    Click OK and now when you click on one of the cells you seleced, you'll see a drop-down arrow. When you click it, you'll see you list of sales people to select from

    hth

  3. #3
    Registered User
    Join Date
    07-21-2009
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: How to limit data on a cell.

    Thanks!

    It works.

    Rgds.

  4. #4
    Registered User
    Join Date
    07-21-2009
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: How to limit data on a cell.

    Me again!

    Just taking advantage of things some of you obviously know a lot better than myself:
    Would it be possible to improve this function being more selective? For instance, if you have some salespeople working in New York, and others in Canada, and you make one of this selective lists on column A where you fill-in with either Canada or New York, and you intend to place your salesperson name on column B. Would it be possible to limit the options on column B as per the value on column A? The easy way would be to concatenate both lists in the original named cell range (in a single column) and then select the concatenated value when working in the form. But this would be quite limited in terms of database management.

    Thanks in advance!

  5. #5
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: How to limit data on a cell.

    Hi Tinchos,

    yep, that can be done. I don't want to repeat here what's been very well written elsewhere, so please check out the link on Debrah Dalgliesh's page www.contextures.com

    http://www.contextures.com/xlDataVal02.html

    She also describes other ways of creating dependent validation, but this one should see you off to a start. Where her example has produce (fruit, vegetables), you should have regions (Canada, New York). Where she has (apples, bananas, etc) you should have your sales people names.

    Let me know how you get on with this.

    cheers
    Last edited by teylyn; 07-23-2009 at 06:36 PM.

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to limit data on a cell.

    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: How to limit data on a cell.

    oops. need more coffee. corrected above.

  8. #8
    Registered User
    Join Date
    07-21-2009
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: How to limit data on a cell.

    Thanks to all for the help!

    All this seems to be so big that I can't even realize where to start from, and how are things called!

    Rgds.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1