Free Microsoft Excel 2013 Quick Reference

Data validation carry with color

Dear All,

Thank you for look at my question, i'm creating a workbook that contained a data validation, there are two work sheets, first called Project and the second called ProjectList, Project worksheet contained a drop down menu and ProjecList worksheet contained the validation data with different background color on each cell.

I'm looking for the solution that the backgrond color of the cell that can auto synchronize to the Project worksheet based on the value i selected from the drop down menu.

I'm attaching the sample of file, please download it and you will see what i'm talking about.

Thanks people in this great forum!


How can I match a data validation selection with a value from the data validation list range. I need to find which row a name is in from the range and then pull more information from the range page row of the matching value.

I have a layout like this:
My information page:
Driver, VIN, ##, Year, Make, Model, LP#, Exp Date
Bob, xqew23weqiiuwer, AB0201, 2008, FORD, E-350, XXX 111, Dec-08

I have the data validation list based on the Driver column. I need to populate another worksheet with the rest of the row information.

Thanks,
Dave

Good Morning All,
I am trying to create a data validation list with a bar graph. I have tried using the IF and Vlookup formula, but its not working for me. I would like to be able to select classroom # from the drop down menu and the bar graph update selected with student’s name and selected data information. I have attached the file. It will be greatly appreciated if any one can help me with this. I have been working on this for about 2 months trying to figure this out.

Hi there

I would like to create a list based on data validation. The
complication is being able to format this depending on the selection. I
cannot seem to get a cell with data validation to change color - custom
number formatting doesn't register an input which has been made from a
cell entry using data validation.

Does anyone know how to do this at all? Your earliest response will be
greatly appreciated.

--
rbekka
------------------------------------------------------------------------
rbekka's Profile: http://www.excelforum.com/member.php...o&userid=14032
View this thread: http://www.excelforum.com/showthread...hreadid=553115

Hi there

I would like to create a list based on data validation. The complication is being able to format this depending on the selection. I cannot seem to get a cell with data validation to change color - custom number formatting doesn't register an input which has been made from a cell entry using data validation.

Does anyone know how to do this at all? Your earliest response will be greatly appreciated.

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!!!

I need help with the excel problem below;

A1= Current number of licenses.
A2= Current annual support amount.
A3= Annual Renewal %. Data validation only allows 3%, 7%, or 10% to be entered.
A4=New total number of licenses. This will be different each time.
A5= New support amount based on the new number of licenses.
Based on what value is in A3 (3%, 7%, or 10%) I want it to choose one of my three corresponding support matrix workbooks; “3%SUPPORT”, “7%SUPPORT”, “10%SUPPORT” and
then using VLOOKUP cross reference the total number of new licenses in A4 and give me the new support amount in A5 which is in the 2nd column of each of the 3 worksheets,
1st column contains the number of licenses.

Below is a simplified example;

A1 = 50
A2 = $200.00
A3= 3% | 7% | 10%
A4= 100
A5=$250.00 This amount auto populates from the 3% support worksheet based on the total number of licenses in A4

3% Support worksheet; A = Number of licenses B=New support amount

A B
99 $242.00
100 $250.00
101 $252.50

7% Support worksheet A = Number of licenses B=New support amount

A B

99 $253.00
100 $251.00
101 $255.75
10% Support worksheet A= Number of licenses B=New support amount

A B

99 $261.00
100 $269.00
101 $272.25

Thank you for your help.

I have a list of products from A-Z. I'm using Data Validation with a List and would like to incorporate an Autocomplete feature.

I tried having my list in column A and using the Insert->Name->Define=MyRange, and then setting the Data->Validation->List=MyRange, but this won't Autocomplete.

Any suggestions?

Thanks.

Here's a question for all of you smart people out there. I am completely new to the whole macros universe for excel and am trying to learn the coding. I associate it with recording actions in photoshop because that is something that I can understand.

Here's the deal. I have created a drop down list using the data validation method. I would like a corresponding graphic to be brought up depending on which item is selected in the drop down. So I thought well, I could record a macro, and then use a simple if then logic to reference that macro if a value is true. Apparently it is not that easy.

Any insight to this inquiry? Thank you in advance for helping a beginner.

I am using the file downloaded from
http://www.contextures.com/excelfiles.html called DV0043 - Data Validation
Combobox With Entry Check. I copied and pasted the example code and created
the combo box but am having one issue. No mater what I select from the drop
down list, when I change fields I get the error message that what I selected
is not a valid value. I've traced the error message in the code but do not
understand why when I moved it to my spreadsheet I would get this error no
matter what I select.

CODE FOLLOWS:
Option Explicit
Dim strTargAdd As String

Private Sub TempCombo_Change()
strTargAdd = ActiveCell.Address
End Sub

Private Sub TempCombo_LostFocus()
Dim rngTarget As Range
Dim lDVType As Long
Dim strList As String
Dim rngList As Range
Dim wsLists As Worksheet
Dim lCount As Long
Dim strOldValue As String
On Error Resume Next

Set rngTarget = Range(strTargAdd)
strOldValue = rngTarget.Value
Set wsLists = Worksheets("Mileage")
lDVType = rngTarget.Validation.Type

***HERE IS WHERE THE ERROR COMES FROM***

If lDVType = 3 Then
strList = rngTarget.Validation.Formula1
rngList = wsLists.Range(Right(strList, Len(strList) - 1))
lCount = WorksheetFunction.CountIf(rngList, strOldValue)
If lCount > 0 Then
' do nothing
Else
rngTarget.Value = ""
MsgBox strOldValue & " is not a valid entry for cell " & strTargAdd
End If
End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Mileage")
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Application.EnableEvents = False
Application.ScreenUpdating = False

If Application.CutCopyMode Then
GoTo errHandler
End If

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With

errHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub

End Sub

I'd appreciate ANY assistance to resolve this problem
TIA!

Hi Excel Gurus,
I am very green @ XL so bear with me. I have created a shipping request
form and added data validation drop down lists for every category (ship to
company name, address, city, etc.) This data was imported from our preferred
carriers online address book. What I am looking for is a way to have the
source list update each time a user enters a new entry and have it placed
into the appropriate list alphabetically. These are routinely shipped to
locations but there are over 100 and growing each day.
Thus far XL appears to be able to do anything except make me a cup of
coffee so I just know what I am asking help for is probably "XL 101" to you
all! Please bear in mind that simply replying with a code will do a fly by on
me!!
Thx in advance. CJ

Hi all,

I'm trying to use the Data>Validation feature in Excel on a range of cells. I want to allow only entries that starts with the letters "MUT" followed by whatever. The following user defined function in VBA is equivalent to the "like" operator in VBA.


	VB:
	
 
    ISLIKE = arg Like pattern 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So I tried Data>Validation>Custom and entered =ISLIKE(B3,"MUT*") in the formula box but I get a message as if Excel was trying to reach some named range...

Can anyone help?
Thanks

Hi Derk. I am having a similar problem to a problem you answered on 12th Feb regarding Data Validation. (val1 and val2)
I have read your reply to xeviva but am still stuck. I have the formula working ok as described in the matching lists workbook. Now i need to drag the val1 and val2 cells down the sheet, but when i do this the val2 cells appear to retain the link with the first val1 cell. I would like the val2 cells validation to be linked to the adjacent val1 cells. Can you help me? Much appreciated!

(appologies if this is the second time i have submitted this posting - had a few problems!)

Hello, I am trying to combine a data validation list with an "IF" function.
Example IF(A1="XXX",...) data should come from name range "AA1" in a pull down menu, IF(A1="YYY"....) data should come from name range "AA2" in the pull down menu.
In A2 ,the user shall only be able to choose from the data relevant to the category chosen in A1.

Is this somehow possible? (I am using Excel 2007)
Thanks a lot for your help.

Hi Excel Gurus,
I am very green @ XL so bear with me. I have created a shipping request
form and added data validation drop down lists for every category (ship to
company name, address, city, etc.) This data was imported from our preferred
carriers online address book. What I am looking for is a way to have the
source list update each time a user enters a new entry and have it placed
into the appropriate list alphabetically. These are routinely shipped to
locations but there are over 100 and growing each day.
Thus far XL appears to be able to do anything except make me a cup of
coffee so I just know what I am asking help for is probably "XL 101" to you
all! Please bear in mind that simply replying with a code will do a fly by on
me!!
Thx in advance. CJ

Changing colour on changing a Data Validation List with VBA

Dear Forum,

As per the Title, I am Adding Data Validation using a Macro but I want to also change the colour on selecting the Items in the desired column F.

Sub AddDV()
'http://www.excelforum.com/newreply.php?do=newreply&noquote=1&p=2319790
Dim Target As Range
Dim LastRow As Long

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    End With

For Each cell In Range("C2", Range("C65536").End(xlUp))
  'Set target = Range("B1:B3")
  Set Target = Range(cell.Offset(0, 3), cell.Offset(LastRow, 3))
  
  With Target.Validation
        .Delete
        .Add xlValidateList, 1, 1, "Fixed Cost,Variable Cost"
        .InCellDropdown = True
        .ShowInput = True
  End With

Next
End Sub
I am getting 90 filled rows instead of 45 in the column F and 45 in Column G when I am running the same code again for the second column G

I want to add Data Validation Items such as :1 Fixed Cost which should be in the Red Colour i.e. Colour Index = 3
2 Variable Cost which should be in the Blue Colour i.e. Colour Index = - 4165632

The Text should have the below Formatting
Horizontal Alignment= Left
Font = "Book Antiqua"
Font Size = 13

I want to get the colour change immediately on changing the Data Validation...

Warm Regards
e4excel

Hi,

I just need a way to control my Auto Filter by what a picked in a data validation list box.

Here the setup: Sheet1 = Summary, Sheet2 = Data

Sheet1 has a data validation box with peoples names in them.
Sheet2 is setup with autofilter

What I want some how is once a name is picked from the list is for the autofilter to filter by this name.

Can someone get me going on this?

Thank You, Mike

I have two data validation lists, one with first names and one with last names. I'm trying to write an IF statement that gives the address when the names are picked (BOTH criteria met) but also shows an error when the names do not match.
I have been playing around with it, but can't seem to get it to work out right.
Thanks for the help

I have formula-driven values in named lists I am using with Data
Validation, Excel 2003. My problem: The original selected response in the
DV-assigned cell stays the same, but its formula-driven value is no longer
correct after formulas in other cells/lists create a change in the values in
my original DV list. Because the originally-selected value in the dropdown
is also generating values elsewhere, this is causing huge problems in my
program. I have hundreds of choices, and they all depend on each other. Is
there any way I can make the DV originally-selected result update
automatically to its new value (i.e. to the recalculated value in the list)
without clicking on the cell containing the dropdown and re-selecting its
equivalent new value?
If there's code for this, please explain as clearly as possible -- I'm
just a babe in that particular woods. Thanks! Brenda

I named a column "resname" without values as of now but later on be filled with names everytime i update the list with new names.

Now i created a data validation > list > and used the source of list as "=resname". but after which i dont see the existing names in the updated list it only shows blank pick list.. Is it because of the blanks? Please help. thanks

Here is my delima,
I already have a data validation setup with the following formula and alert
=COUNTIF($F$22:$F$15000,F22)=3
This account number already exits!

I would also like to add a formula that would alert the user if the length of the account number is incorrect. Is it possible to add this to the data validation to the same cell or do I have to do it in a separate cell.
=IF(LEN(F22)=7,"","Account Number is not the correct length")

Frances

I'm using a data validation list and i'd like to have the cell obtain all the charecteristics (not just the value) of the selection from the list.

i.e. If the list is composed of the values one, two and three. If a user selects "one" the word "one" appears in the cell and the colour turns to red. Pick two and "two" appears in green cell. ...

Can this be done?

How would I go about doing this?

Thanks in advance for your help

I have a unlocked cell, with Data Validation, List. With sheet protection
activated I cannot "add" to the cell contents supplied from the List
function. If sheet protection is off, this is possable. Is there a way to
have this feature with sheet protection turned on (activated)?

The scenerio in the spreadsheet I am designing the user selects a "type" from a data validation list and then depending on that selection they select a "subtype" from data validation list which I need to vary depending on the chosen type, however I have had no success implementing this so far. An alternative approach that I have not attempted is to alter the content of the cells referenced in the data validation list with IF functions, however when one "subtype" list is shorter then another it will cause unnecessary spacing in the drop down list, would it be possible to use a COUNTA in the data validation? I hope I have been clear in what I wish to achieve, any recommendations and help would be very appreciated

I am using the data validation to create a drop down list, what I need to do is if cell D9 = Senior I want one of the choices in the drop down list not to be allowed

So for example there is a code called ASI in the drop down, I dont want users to be able to select ASI from the drop down, if Senior is already selected. Is there anyway this can be done ?