Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Data validation list limit?

Is there a limit to the number of items that can appear in a data validation
drop-down? My source list can grow to be up to 1500 items long, and I don't
see any information in excel help or on this board about whether the
resulting data validation drop-down will be able to handle so many items.

TIA


Post your answer or comment

comments powered by Disqus
How do I expand the number of entries in a Data Validation list when the limitation is exceeded?

I have a workbooksheet with music Genres as below
Blues,Jazz,Classical,Folk,Country,Cowboy,Rock,Rock and Roll ,Hard Rock,Country Rock,Folk Rock,Heartland Rock,Blackened Thrash Metal,Pop,Alternative Rock,Soft Rock,Progressive Rock,Pop Rock, Adult Alternative, Folk Blues, Post Grunge

They are entered by selecting from a Validation List Dropdown.
I want to add more options but have exceeded the limitation.

I am using Excel 2003

Please advise.

Validation List.JPG

I have a data validation list in B31 "material type" that has 4 options (Adhesives, Metals, Backer Board, & Grout). Based on that selection F31 needs to display a list of "descriptions" that match that "material type". The list is in book "database.xls!sheet3". Material type list is Column A, Description is in Column E. I know that you can't link to an unopened workbook in data validation, & I've been trying to code it, but again the lack of expierence & knowledge has frustrated me again :P

This code to get the info for the file to open

	VB:
	
 GetValue(path, file, sheet, ref) 
    Dim arg As String 
     
    If Right(path, 1)  "" Then path = path & "" 
    If Dir(path & file) = "" Then 
        GetValue = "File Not Found" 
        Exit Function 
    End If 
     
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _ 
    Range(ref).Range("A1").Address(, , xlR1C1) 
     
    GetValue = ExecuteExcel4Macro(arg) 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
&


	VB:
	
 TestGetValue() 
    p = "h:users" 
    f = "database.xls" 
    s = "Sheet3" 
    Application.ScreenUpdating = False 
    For r = 1 To 50 
        For c = 2 To 2 
            a = Cells(r, c).Address 
            Cells(r, c) = GetValue(p, f, s, a) 
        Next c 
    Next r 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help is much appreciated, if I am missing some important information (which is very likely) I'll do my best to explain to my limited ability,
Thanx.

Debra Dalgleish > wrote in message >...
> Perhaps you could enter the formula in an adjacent cell, then refer to
> that cell in the data validation formula box.
>
> amit wrote:
> > I am trying to generate "Drop down list" using formula.
> >
> > The path I am using is :
> >
> > Data > Validation ---List ----Formula
> >
> > It seems there is limitation on no. of characters, which could be
> > typed in this FORMULA field.
> >
> > Can someone help me in generating the list using someother technic.
> >
> > Thanks in advance for your replies.

Debra,

Thanks a lot for your reply.

I tried that but it doesn't help for list of values.

More specifically:

I have 12 columns, each containing a list of 100 values (1 value in a
cell).

Based on the value of a cell, different from the cells mentioned
above, in range of 1 to 12 one of the list is to be selected in Drop
down list (using Data > Validation ---List ----Formula).

The above mentioned field at the end of path allows only 8
IF-THEN-ELSE type statements.

I tried using Index, Match, address functions etc. but I am not able
to get the solution.

Hi All,

Is there any limit for number of items to place in data validation list?
Since I tried with 90 items to assign in datavalidation list but I couldn't.

Please share your thought or ideas.

Advanced Thanks,
Vinod

I am trying to generate "Drop down list" using formula.

The path I am using is :

Data > Validation ---List ----Formula

It seems there is limitation on no. of characters, which could be
typed in this FORMULA field.

Can someone help me in generating the list using someother technic.

Thanks in advance for your replies.

Hi,

I'm developing a spreadsheet using Data Validation Lists to allow people to select their Fantasy Football teams and what I'd like to do is when they have selected any players, they no longer appear in the drop down list.

Any help would be gratefully appreciated because I've been searching for ages and haven't found anything, probably because I'm not really sure what I am searching for.

Many thanks

Matt

I have a named range that includes columns A, B, C, and D - Called QB. The name of the tab which the data lives on is also called QB.

In another sheet, I have a data validation list in a column titled "Position" with the following entries: QB, RB, WR, K, DST, TE.

Next to the Position column is the Player column. When the user selects a position from the drop down list, the Player column is populated with a data validation list of all the players from the selected position (the data validation used INDIRECT to populate the list).

I want to use VLOOKUPs to populate other columns such as Bye, Salary, and Team based on the named ranges (which I know how to do).

The problem is, how do I limit the lookup of the "Player" so that the player validation list does not list all of the columns in the named range?

Hi,

I'm trying to put together a spreadsheet that will format the colour of a row based on a value in a data validation list.

The problem is that I am limited by excel 2003. It only allows 3 conditional formats. I need to have a selection of 5 - 8 colours for the different options in the data validation list in column F. If possible I also want to have a conditional format in place for column I. If "h" is selected then it overrides the conditional formatting directed in column F and turns the row a specific colour. Am I asking Excel 2003 to do too much?

I suspect that a VBA code might sort this out easily, but I know little about VBA. I already have a VBA date stamp code in this spreadsheet, so the VBA code will need to be added to and run alongside the existing code. I need something that is relatively people proof and will allow for the insertion of new rows of data within the spreadsheet and at the bottom of the spreadsheet.

Any help is much appreciated. I have attached my spreadsheet so far in excel 2003 format if it is any use.

Many Thanks In Advance,
Joel

I am trying to generate "Drop down list" using formula.

The path I am using is :

Data > Validation ---List ----Formula

It seems there is limitation on no. of characters, which could be
typed in this FORMULA field.

Can someone help me in generating the list using someother technic.

Thanks in advance for your replies.

Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<425E695F.6060205@contexturesXSPAM.com>...
> Perhaps you could enter the formula in an adjacent cell, then refer to
> that cell in the data validation formula box.
>
> amit wrote:
> > I am trying to generate "Drop down list" using formula.
> >
> > The path I am using is :
> >
> > Data > Validation ---List ----Formula
> >
> > It seems there is limitation on no. of characters, which could be
> > typed in this FORMULA field.
> >
> > Can someone help me in generating the list using someother technic.
> >
> > Thanks in advance for your replies.

Debra,

Thanks a lot for your reply.

I tried that but it doesn't help for list of values.

More specifically:

I have 12 columns, each containing a list of 100 values (1 value in a
cell).

Based on the value of a cell, different from the cells mentioned
above, in range of 1 to 12 one of the list is to be selected in Drop
down list (using Data > Validation ---List ----Formula).

The above mentioned field at the end of path allows only 8
IF-THEN-ELSE type statements.

I tried using Index, Match, address functions etc. but I am not able
to get the solution.

I have following data in my excel, attached:
Sheet "Seat_Pool":Columns:
Seat_No: Distinct entries, with no repeats
Group_Head: Head of the group to whom Seat belongs.
Status: Status of a seat VACANT or OCCUPIED.
In reality this list 350+ and always growing, Status changes all the time but seat_no & group_head mapping rarely changes.

In Sheet "Seat_Assignment":
What I want is that "Seat_No" Column (Column B), should have a Data Validation list dropdown based on Group_Head=Harry and Status=Vacant.

I believe my approach should be in following order:1. Auto-Copy or Auto-Generate seat_no of matching criteria (Group_Head=Harry and Status=Vacant), in some column. Lets Call it HARRY_VACANT_LIST
2. Sort HARRY_VACANT_LIST
3. Remove Duplicates
4. Give name definition to HARRY_VACANT_LIST, make this as dynamic so as to avoid blanks.
5. Use this new name definition in Data Validation for Seat_No
Limitations:1. Can't add new columns in any sheet. (Have seen earlier example on this forum at http://bit.ly/vZa6mI, didnt really like the idea of having a new column C called Validation_KEY)2. Can't change order of column in any sheet - I am already using VLOOKUP at many places
3. Will prefer a NON-MACRO approach.
Sample File Attached:
paragb_seat_sample.xls

My earlier post on this subject did not yield me an answer, so I am trying again. Here is the link to that post “Add Filter To Cascading Combo Boxes”. I hope it's not bad practice to start a new post instead of continue the old one. If it is, then I hope someone merges these threads instead of deleting this one.

This time instead of posting my real spreadsheet, I am posting a much simpler example, hoping for a better response.

Please download the attached example spreadsheet. On the workbook “Main” you will see cell “D2” is highlighted in yellow. That combo box (based on a data validation list) shows all the divisions that can be chosen. If you go to the workbook “CSI” you will see a sample of the data that I am working with. Each row of data that starts with a “00” is in Division 00, and each row that stars with “01” is in Division 01, etc.

Once a division is chosen in cell “D2”, then cell F2 looks up a named range. Then the green cells (D6 through D15) are limited to only those items that fall within the division that is chosen in cell “D2” by use of the data validation formula “=INDIRIECT($F$2)”

I am looking for a way to filter the results of these second combo boxes (data validation lists D6-D15) further. Instead of showing all the results of the named range which is shown in cell “F2” I would like it filtered. The filter would be based on whether or not the last column of data for any given row has a “1” in it. It doesn't have to be a "1", I'll accept any way that you can make this work. I am just using a "1" for this example. Whether or not a row contains a "1" in column "J" will change from time to time, and I am thinking that this will be an easy way to indicate, or flag, whether or not a row is "Preferred", but I'm open to other suggestions.

If the cell in that row, row “J” on worksheet “CSI”, contains a “1” then I would like it to be displayed in the data validation lists in cells D6 through D15. If the cell for that row of data is blank then I do not want it to show up in the data validation list combo box.

To complicate this further, I would like a way to easily switch this filter on and off. I imagine this would filter would be turned on or off based on what is selected in an option button group (like I’ve got highlighted in blue in cell “D4”, whose results are tied to cell F4), or through an additional combo box or data validation list.

I'm pretty stuck from moving forward on this project until I get over this hurdle, so I appreciate and help you can provide.

Thanks, Spence

Hello everyone,

I am looking for a way to select multiple items from excel Data Validation List and have the information calculated based on what was selected from the data validation list.
So Example, If we select apples, oranges and grapes in a cell A, and apple is $4, orange $2 and grapes, $2, we need Cell B to show the total cost for the three items selected in cell A.

So far I was able to find this link:
http://blog.contextures.com/archives...lidation-list/
But I still need help with being able to have the information calculated based on each item that's selected, or multiple items that were selected.

Is there a solution? Or is this impossible?

Hello

i have the below problem

i have a particular set of data (Friends names from D2 to D25) (and a their spouses name on the next cell ie. E2 to E25)

Now i have created a Data validation list in "A1" for the friends names
in "A2" i have done a vlookup of friends name in "A1" to the corresponding wife's name on the right

What i want? - to print A1 and A2 for each friend by changing the name in the list in "A1"
Instead of changing every name one by one and then giving print command i want to run a macro

Someone person told me to try the below code. I know its wrong cos its not working properly.

So can someone please help me out


	VB:
	
 
Sub Print() 
    Dim x As Integer 
    For x = 1 To 24 
        Range("A1").Value = Cells(x, 24).Value 
        ActiveSheet.PrintOut 
    Next x 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Trying to write VBA code to have the contents of C2 paste (pastespecial) into B2 if the user selects "P" which is a selection possibility for the data validation list that resides in E2. Would like users of the form to be able to enter values into B2 unless "P" is selected from E2, at which time it would lock the cell and copy in value from C2 which is a formula that resides in a hidden column. If they select any other value the cell would unlock and the contents would clear. I have some code written, but have a feeling that due to my experience with VBA, it wouldn't be of any assistance here.

I am trying to put in a multi option pick list or data validation list into an Excel S/sheet, and I am lost. I want to be able select multiple options that have been set up for the list, and be able to do this in a random order. I have investigated this and all I can see is that I need to use a VBA code- but this level of skill is beyond me. I have attached the s/sheet that I am trying to do this in. I have got as far as creating a data validation list on the tab lists in column N and want to be able to multi pick from the drop down on the tab 'Data' in column Y. Feeling rather frazzled.

I'm trying to create the following functionality in reference to the following:

LongList ShortList dog chicken dog dog pony donkey pig horse donkey ostrich horse pig ostrich pony chicken chicken pony pony
1. ShortList is a sorted list of the unique elements in LongList. 2. ShortList also serves as data validation list, meaning user can add to LongList by selecting ShortList items from drop-down menu.
3. ShortList is dynamic, in the sense that if user adds new item to LongList, it shows up on ShortList, and is thus available from the drop-down menu.
4. User can add overwrite an item anywhere on LongList with a new item that is not on ShortList, and ShortList will update automatically.

I'm having difficulty with #4, which seems a kind of Catch-22 situation. Because LongList is using the Data Validation feature (to get the dropdown menu), it seems it's not possible for the user to be able to add an item to the middle of the list that is not on ShortList, i.e., the data validation list.

I understand that I could require the user to add only to the bottom of LongList (thus allowing the range of data validation to grow dynamically), but that's not what I want.

I've looked over the various range.validation properties hoping to be able to turn something off to allow user to enter a value in the middle of ShortList, but nothing jumps out at me.

Any ideas?

I got a huge list of Names in my data validation list drop-down. Is there any way to use autocomplete just to save some time? For example, if user types P in the cell, it will show a list of names starting with P and then narrows down as more letters are typed.

I do not want to use a combobox, since there are hundreds of cells with these in-cell data validation list drop-downs

I am writing a Macro in excel VBA that creates a data validation list in a specified cell. The program then prompts the user for the cells which contain the contents of the data validation lists. The same rows containing the list contents are then to be hidden from view. However, when I try to rerun the macro multiple times, each time I select a new range for contents, each of the proceeding lists then refers to this range. **I DO NOT** want this to happen.

I wrote this line of code to prevent this:

Code:

	VB:
	
 ThisWorkbook.Names 
    strRngNumLbl = strRngNmLbl + 1 
Next nm 
strRange = strRange & strRngNumLbl 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Where strRng is the name of the range to refer to when adding to the data validation. However, for some reason this does not work. I thought this would work because it would create independent names for each of the ranges to be added to a list. But it does not...

Here is the entire code:

Code:

	VB:
	
 
 
Sub CreatDropDownList() 
    Dim strRange As String 
    Dim celNm As Range 
    Dim celNm2 As Range 'use only if necessary
    Dim celRng As Range 
    Dim strRngNumLbl As Integer 
    Dim nm As Name 
     
On Error Goto pressedCancel: 
     
    Set celNm = Application.InputBox(Prompt:= _                     "Please select a cell to create a list.", _              
         Title:="SPECIFY Cell", Type:=8) 
     
    If celNm Is Nothing Then Exit Sub 
     
     'Inserts a copy of the row where the drop down list is going to be
    celNm.EntireRow.Copy 
    ActiveCell.Offset(1).EntireRow.Insert '?
     
     'moves inserts N/A to the left of validation list
    celNm.Offset(0, -1).Value = "N/A" 
     
     'cell range equal to nothing
    Set celRng = Nothing 
     
     'asks user to determine range of strings
    Set celRng = Application.InputBox(Prompt:= _         "Please select the range of cells to be included in list.", _       
     Title:="SPECIFY RANGE", Type:=8) 
     
    If celRng Is Nothing Then Exit Sub 
     
    On Error Goto 0 
     
    strRange = "DataRange"     strRngNumLbl = 1 
     
     'Increments strRngNumLblb for the number of names present in the workbook to
     'ensure list is not referring to duplicate ranges
    For Each nm In ThisWorkbook.Names 
        strRngNumLbl = strRngNmLbl + 1 
    Next nm 
    strRange = strRange & strRngNumLbl 
     
     'user defined data range is now called strRange, refer to it as Range(strRange)
    ThisWorkbook.Names.Add Name:=strRange, RefersTo:=celRng 
     
     'format the refernce name for use in Validation.add
    strRange = "=" & strRange           celNm.Offset(-1, 0).Select 
     
     'Add the drop down list to the target range using the list range
    celNm.Validation.Delete 
    celNm.Validation.Add xlValidateList, , , strRange 
     
     'hide the range where the list came from
    celRng.EntireRow.Hidden = True 
pressedCancel: 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any suggestions?

I am writing a Macro in excel VBA that creates a data validation list in a specified cell. The program then prompts the user for the cells which contain the contents of the data validation lists. The same rows containing the list contents are then to be hidden from view. However, when I try to rerun the macro multiple times, each time I select a new range for contents, each of the proceeding lists then refers to this range. **I DO NOT** want this to happen.

I wrote this line of code to prevent this:

Code:

For Each nm In ThisWorkbook.Names strRngNumLbl = strRngNmLbl + 1 Next nm strRange = strRange & strRngNumLbl
Where strRng is the name of the range to refer to when adding to the data validation. However, for some reason this does not work. I thought this would work because it would create independent names for each of the ranges to be added to a list. But it does not...

Here is the entire code:

Code:

Sub CreatDropDownList() Dim strRange As String Dim celNm As Range Dim celNm2 As Range 'use only if necessary Dim celRng As Range Dim strRngNumLbl As Integer Dim nm As Name On Error GoTo pressedCancel: Set celNm = Application.InputBox(Prompt:= _ "Please select a cell to create a list.", _ Title:="SPECIFY Cell", Type:=8) If celNm Is Nothing Then Exit Sub 'Inserts a copy of the row where the drop down list is going to be celNm.EntireRow.Copy ActiveCell.Offset(1).EntireRow.Insert '? 'moves the cell to the appropriate location celNm.Offset(0, -1).Value = "N/A" 'cell range equal to nothing Set celRng = Nothing 'asks user to determine range of strings Set celRng = Application.InputBox(Prompt:= _ "Please select the range of cells to be included in list.", _ Title:="SPECIFY RANGE", Type:=8) If celRng Is Nothing Then Exit Sub On Error GoTo 0 strRange = "DataRange" strRngNumLbl = 1 'Increments strRngNumLblb for the number of names present in the workbook to 'ensure list is not referring to duplicate ranges For Each nm In ThisWorkbook.Names strRngNumLbl = strRngNmLbl + 1 Next nm strRange = strRange & strRngNumLbl 'user defined data range is now called strRange, refer to it as Range(strRange) ThisWorkbook.Names.Add Name:=strRange, RefersTo:=celRng 'format the refernce name for use in Validation.add strRange = "=" & strRange celNm.Offset(-1, 0).Select 'Add the drop down list to the target range using the list range celNm.Validation.Delete celNm.Validation.Add xlValidateList, , , strRange 'hide the range where the list came from celRng.EntireRow.Hidden = True pressedCancel: End Sub
Any suggestions?

best way of making hyperlinks from data validation list...?

i have a solution, but i'm not sure if it's the best...

picking an item from data validation and having it show as a hyperlink...

???

thank you.

i am trying to zoom the screen to make the items listed in a data validation list visable. as for size is too small to be viewed properly.

is there away of zooming if i click on the cell with the validation list in so i can select the item i want then the zoom deactivates on my selection of that item ?

hopefully i have explained that correctly, and anyhelp will be gratefully received.

Hey all!

I'm working on a spreadsheet to allow me to better organize a bunch of posters that I have. The spreadsheet is designed so that each movie poster is assigned a number, 1-160. I have set up a data validation list that allows me to select a movie poster and when I select the title the number assigned to it is returned to me in another cell. However, the reference list for the Data Validation is sorted in order of assigned number, not alphabetically, but I would like the list to be sorted alphabetically. Is there any way to do this?

I know typically the source list has to be alphabetized, but the posters change frequently, as I work at a movie theater, and so to have the source list alphabetized just really isn't feasible.

Any help at all would be greatly appreciated!

Thanks in advance,
Philip

Hi All-

I'm a relative n00b so please go easy.

ISSUE SUMMARY:
In Excel 2010, I have a spreadsheet with two tabs: the first tab contains two columns: A which lists the names of individuals and B which lists whether or not they are available (a simple "Yes" or "No" data validated list). So something basic like:

A B
Bob Yes
Pete No
Jane No
Sue Yes

The second tab has a single column, C, against which I want to apply data validation to but only show those people that are available. In other words, I want to filter out those from the first tab's column A whose associated column B is "No." So, C's dropdown options using the sample set about would be "Bob" and "Sue" only.

QUESTION:
Is there a simple, eloquent, way to do this WITHOUT using VBA, advanced filtering, etc. even if I have to create a working data set elsewhere? It seems like I should be able to use a named range and filter it somehow in a formula applied to the data validated list, but I can't figure it out nor find any similar solution online.

Thank you in advance!


No luck finding an answer? You could always try Google.