Free Microsoft Excel 2013 Quick Reference

Conditional Data Validation List

I am trying to make a list show up in one column based on the user's choice in another column. The list is called "Display" defined in the Name Manager. I have this in the Source field in the Data Validation wizard:

=IF(OR(D4="AA (homework - Aplia)",D4="AA (homework - Other)",D4="AA (Assessment)"),Display)

I am getting an error that says: The list source must be a delimited list, or a reference to a single row or column.

Can I not make the appearance of the list conditional in this way?


Post your answer or comment

comments powered by Disqus
I need to reset multiple data validation lists (which are indirect references to one another... read: conditional data validation lists).

I need a button per list, I currently have 13 lists.

I also need a full lists button.

AKA. I need to be able to reset one list at a time, or all lists on one click.

I'm not even sure where to start, any help available?

Thanks in advance.

Hi,

I am trying to create a conditional data validation list that will select 2 different ranges depending on the content of Column C.

If I type "Plate" I want column D to have the data validation range: tubeg
If I type "Tubular" I want column D to have the data validation range: tubeg
If I type "Section" I want column D to have the data validation range: sectiont

Currently I am using the data validation formula:
=IF(OR(C6="Plate",C6="Tubular"),tubeg),IF(C6="Section",sectiont)

I am getting the error message saying:
You may not use unions, intersections, or array constants for Data Validation criteria.

Any help would be greatly appreciated.

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'd appreciate any help you can give here. With Excel 2010 I am using Conditional Formatting to change the color of some cells (e.g. A2 & A3) in a column whenever they contain text (i.e. "No Blanks"). This works fine until I use filtering or sorting on the column - when I do, the Conditional Formatting stays with the original cells (A2 & A3), rather than moving with the correct row of data. Is it possible to have the Conditional Formatting move with the data during a sort?

The exact same thing is happening with cells to which I have applied a Data Validation List. Is it possible to have the Data Validation move with the data during a sort?

I suspect this has something to do with relative vs. absolute references, but don't know how to fix it. Thanks!!!

Hi,

I was wondering if anyone would be able to help me.

I am looking to create dependent data validation lists but I'm not sure how to go about doing this.

What I need to do:

Suppose in Cell F10, I can choose from a choice for 5 countries (i.e. UK, USA, France, Mexico & Canada) from a normal Data Validation List.

Based on the selection in F10, immediately below that cell in F11, I want a conditional or dependent validation list that will enable me to choose the relevant company.
So for example, if I choose UK in cell F10, I then want a validation list in F11 which will be companies that only refer to that country (i.e. Company A).

Is it possible to do this? Is it also possible to these dependent validation lists for further cells below such as in F12 dependent on the selections made in F10 and F11?

I would appreciate any help on this matter.

Many thanks.

rkapadia16.

I have two cells that allow the user to select a month . Both are using data validation to limit the months they can choose. However, I would also to limit the second cell, so that users are foreced to select a month that occurs after the month selected in the first cell.

I know that you can use INDIRECT to do conditional data validation, but I think that would cause me to have a separate list for each month.

Any thoughts?

Thanks

I'm trying to create a conditional data validation for a cell ($D4) based on
the entry in cell ($B4). There are three possible entries for cell $B4.
I've created a named list for $B4 and based on the entry I want three
different conditional data validation types for cell $D4:

1. Allow a range of numbers (1 to 255)
2. Drop-down list A
3. Drop-down list B

I'm fairly proficient with Excel; however, I'm a very novice VBA programmer.

Do you have any suggestions on the above situation?

Rick

I've recently developed and programmed 5 analytical procedures in Excel
environment (XL 2003, Windows XP).
For simplicity, let us assume that the following depicts the applicability
of the 5 packages:
SERIES FOR INFLOW PROGRAM

Series_1........Prop............Uniform...............BS.xls

Series_2........Prop.......Steady Co-Axial.........GS-Stdy-Prop.xls
Series_2........Cav........Steady Co-Axial.........GS-Stdy-Cav.xls

Series_2........Prop.......Unsteady Periodic......GS-Unstdy-Prop.xls
Series_2........Cav........Unsteady Periodic......GS-Unstdy-Cav.xls

The 5 XL files reside in a common folder.

The data validation list under SERIES would have Series_1, Series_2.
The list under FOR depends on the selected item under SERIES.
The list under INFLOW depends on the selected item under FOR.
The displayed file name under PROGRAM dependes on the selected item under
INFLOW. Then click and open the file, if so desired.

For example, if the following selections are made: "Series_2" "Prop"
and "Unsteady Periodic", the file name "GS-Unstdy-Prop.xls" should appear
under PROGRAM. Then click to open the file.

One perhaps could use Excel nested conditional IF statements to sort out the
applicable lists. Another approach would be to use data AutoFilter.

Can you think of a more elegant way?

Any suggestions? Thank you.

Hi,

The cells in column B in my spreadsheet can only contain numeric values (I have created a data validation list where only 1 or 0 is permitted). Another column, C, is based on the value in column B, i.e. if a cell in column B has a 1 in it, you're supposed to choose between 1 and -1 in column C. If the cell in column B has a 0 in it you must choose 0 in column C.

What I'd like to do now is to conditionally format the data validation list for all the cells in column C, so as to only display the options 1 and -1 in col C if 1 is entered in col B and only display option 0 in col C if a 0 is entered in col B.
How do I do this?

Hi all, can anybody help me figure out how to colour code cells with drop-down data validation lists? I've got a drop-down box with 3 options: "agree, disagree, unsure"; I've formatted the source list so that agree is green, disagree is red, and unsure is a neutral colour. But this doesn't copy over into the sheet with the drop-down list. Is there a way to do this, using conditional formatting or some other approach?

Thanks in advance!

stuart

Hi,

I'll simplify my problem to this example:

Say there is three drop down lists created from data validation in sheet1 (say in cell A10, B10 and C10 and the data in each list is used to modify cell A1 (say default is a number 1) above with various conditional if statements. So cell A1 will change based on whatever is selected from these 3 drop down lists (multiply by this, add that, etc.). That is all setup fine. Now what if I want to create a button or something else clever that by clicking on it resets all the data validation lists to their first selection, how would I do this? So list1 might currently have option 4 selected, list 2 option 7, list 3 option 9 or whatever, and hitting this reset button puts them all back to their first choice (but still allows you to continuously select the options or reset repeatedly if wanted).

Is there a way for the DATEDIF formula to correctly pickup values out of a Data Validation List? Currently I am getting a NUM! error, and not sure how to correct the problem.

The eventual goal will be to run a conditional format based on the user choice of the DV List, but I'm beginning to fear that this may not be possible.

I am trying to highlight the row if TODAY() is before the date from the DV List in Column A.

Any help is appreciated.

Hello all,

I would like to have a data validation list if conditions are false. Is there a way to do that? Thanks in advance for any help!!!

Mark

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


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