Multiple Data Validation In One Cell

ir121973

Active Member
Joined
Feb 9, 2008
Messages
371
Hi, I wonder whether someone may be able to help me please.

I'm using the attached file to record staff resource.

www.box.com/s/4eauffqp2q9wbp5eat39

If you change the value in column I to either "BC", "BNC" or "OH", the value in column J will change to "--Select--", prompting the user to select a value from the drop down menu. The drop down values in this column are retrieved from the "Lists" sheet via a INDIRECT Data Validation formula.

I do however have one problem.

If you change the value in column I to "P", you'll see that the value in column J changes to "Enter the Project Code", which via my VBA code allows the user to populate this with free text.

The problem I have is that users are entering all manner of descriptions, which cause problems when evaluating the data.

So what I'd like to do is to maintain the exisiting functionality, but when they are asked to "Enter the Project Code", I'd like the user to be resticted to 4 alphanumeric characters but I'm a little unsure about how to do this because I'm already using the INDIRECT Data Validation in this cell.

I just wondered whether someone could possibly take a look at this please and offer some guidance on how I may go about this.

Many thanks and kind regards

Chris
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
You could use something like this to change the Data Validation based on what is in Column I. It just modifies the corresponding cell in Column J.

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    
    If Target.Column <> 9 Then
        Exit Sub
    End If
    Set rng = Target.Offset(0, 1)
    
    With rng.Validation
        .Delete
        
        If Target.Value = "P" Then
            .Add Type:=xlValidateTextLength, _
                AlertStyle:=xlValidAlertStop, _
                Operator:=xlLessEqual, _
                Formula1:="4"
        Else
            .Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, _
                Formula1:="List Data Goes Here"
        End If
        
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
End Sub
 
Upvote 0
Hi @AD_Taylor, thank you for taking the time to reply tomy post and for th solution.

I've tried the code, but unfortunately doesn't quite achieve what I'm looking for. With the implementation of the code it removes the drop down menu needed for those values previously listed and doesn't restrict the characters to alphanumeric 4.

Many thanks and kind regards

Chris
 
Upvote 0
Yes I forgot to mention that you need to change the bit where it says 'List data goes here'. This should be set to whatever your INDIRECT formula was.

Then when you enter P the corresponding cell in Column J should be limited to 4 characters. I tried entering hello and it was rejected.
If you enter anything other than P then the data validation is reset to the drop down list.

HTH!
 
Upvote 0
Hi, thank you for your continued help with this. My apologies for being a bit slow, but my formula in J7 would be =INDIRECT($I7), obviously changing the cell refernce to match the row.

Could you perhaps tell me please how I would incorporate this into the code you kindly provide to taker account of the change in rows.

Many thanks and kind regards

Chris
 
Upvote 0
This should get it to align correctly with the row

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    
    Const strFormula As String = "=INDIRECT($I@)"
    
    If Target.Column <> 9 Then
        Exit Sub
    End If
    Set rng = Target.Offset(0, 1)
    
    With rng.Validation
        .Delete
        
        If Target.Value = "P" Then
            .Add Type:=xlValidateTextLength, _
                AlertStyle:=xlValidAlertStop, _
                Operator:=xlLessEqual, _
                Formula1:="4"
        Else
            .Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, _
                Formula1:=Replace(strFormula, "@", Target.Row)
        End If
        
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
End Sub

Although personally I don't see how that formula gets you a list of data?
 
Upvote 0
Hi @AD_Taylor, thank you very much for your continued help with this.

The solution works great. It's just what I was trying to achieve.

ALl the best and kind regards

Chris
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top