Data Validation limit

I have never encountered a limit to the length of individual entries in a drop-down menu, nor a limit to the total number of entries. That assumes that you have followed the proper syntax for defining a list... validation is VERY picky about how you define a list: no unions, no intersections, no array formulas, blah blah, blah. It wants a comma de-limited text string, or a reference to a range.

If you are dealing with more entries than are visible in the text field of the validation window, consider referring to a range of cells instead, if for no othe reason than to make it easier to edit your list in the future.

the same holds true for the validation definition: consider writing long formulas in one or more cells, then reference the final boolean result from the data validation window.

Thank you Hatman.

You are welcome!

Hatman,

When I use list, I can only put in 254 Characters (including spaces). Is there a way to add more?

Thank you,
Craig

Are you typing directly in the Data Validation field?

Hatman,

When I select the cell and then select Validation - I use tab 1 (Settings), Allow (List) then in the Source field then I input:

ACID, AIR, AIR, ALUM, AMMONIA, ARGON, ASBESTOS FREE INSULATION, BOILER FEEDWATER, BOILER SLOWDOWN, BRINE, BYPASS, CAUSTIC, CARBON DIOXIDE, CHEMICAL FEED, CHILLED WATER, CHILLED WATER RETURN, CHILLED WATER SUPPLY, CHLORINE, CHLORINE SOLUTION, CIRCULATING WATER, CITY WATER, COLD WATER, COLD WATER RETURN, COLD WATER SUPPLY, COMPRESSED AIR, COMPRESSED AIR, CONDENSATE, CONDENSATE DRAIN, LO PRESS CONDENSATE, IVIED PRESS CONDENSATE, HI PRESS CONDENSATE, CONDENSATE RETURN, CONDENSATE SUPPLY, CONDENSER WATER, CONDENSER WATER RETURN, CONDENSER WATER SUPPLY, COOLING WATER, COOLING WATER RETURN, COOLING WATER SUPPLY

It only allows only 256 characters.

Oh. Well I learned something new... I am not surprised that the limit is somewhere around 256 characters. But since you can only see about 35 characters in the field at any given time, I have never tried entering much more than that directly. It gets to be a PAIN, if you spelled condensate wrong, and had to go back and fix it.

As I mentioned above, for long lists, I recommend entering the data in a range of cells... in your case, I would say it is your only option. if you have never done it this way, relax: it's easy. Simply choose a range of cell soff to the side, and start entering the item sin your list, one per cell, no blank cell sin-between. When you are done, open the data validation dialog, select List, and then click on that little red box at th eright-hand end of the field... then select the range of cells that contains your list... hit enter and you are done. the beauty is that if you change an entry in one of those cells.

It was THIS application that I was referring to when I indicated that I had never encountered a limit... you can theoretically have up to 65,536 members in a list (though that woul dbe SILLY).

Using a variation of this method that involves Named Ranges, it is also possible to make cascading menus...

Hatman,

When I select the cell and then select Validation - I use tab 1 (Settings), Allow (List) then in the Source field then I input:

ACID, AIR, AIR, ALUM, AMMONIA, ARGON, ASBESTOS FREE INSULATION, BOILER FEEDWATER, BOILER SLOWDOWN, BRINE, BYPASS, CAUSTIC, CARBON DIOXIDE, CHEMICAL FEED, CHILLED WATER, CHILLED WATER RETURN, CHILLED WATER SUPPLY, CHLORINE, CHLORINE SOLUTION, CIRCULATING WATER, CITY WATER, COLD WATER, COLD WATER RETURN, COLD WATER SUPPLY, COMPRESSED AIR, COMPRESSED AIR, CONDENSATE, CONDENSATE DRAIN, LO PRESS CONDENSATE, IVIED PRESS CONDENSATE, HI PRESS CONDENSATE, CONDENSATE RETURN, CONDENSATE SUPPLY, CONDENSER WATER, CONDENSER WATER RETURN, CONDENSER WATER SUPPLY, COOLING WATER, COOLING WATER RETURN, COOLING WATER SUPPLY

It only allows only 256 characters.

Click to expand...

Enter each item in a cell of its own on a separate sheet. Then select all of the cells with an entry, go to the Name Box on the Formula Bar, type SourceList, and hit enter.

Now:

1) Select the cell where you want a dropdown list.
2) Activate Data|Validation.
3) Choose List for Allow.
4) Enter the following in the Source box:

=SourceList

5) Click OK.

You ca, by using a named range, put the source list on another sheet.

Create a list down one column holding your choices -- say it runs A1:A125 on Sheet4

After entry, click Insert | Name | Define, then give it a name -- WaterType, for instance -- in the Names box, and in the Refers to box enter: =Sheet4!$A$1:$A$125

Click OK.

Now, in your D.V., select List and in the source box, enter: =WaterType