How to limit data on a cell.

**Tinchos** · 07-21-2009, 09:11 PM

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

**teylyn** · 07-21-2009, 09:21 PM

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

**Tinchos** · 07-22-2009, 08:17 AM

Thanks!

It works.

Rgds.

**Tinchos** · 07-23-2009, 03:44 PM

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!

**teylyn** · 07-23-2009, 05:16 PM

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