Validation query

Lee(UK) · Aug 1st 2004

Hi All,

Can anyone help out with this one. In the very shortest terms, what I am trying to do is validate entries in cells that are populated by a formula.

The basics of the sheet is that it contains 5 columns and 11 rows, the 1st column contains fixed data, the second is validated by a list using a named range contained on a different sheet. The 3rd/4th and 5th columns are populated by a vlookup formula, by taking the data from column 2 and looking up data contained on the same sheet as the named range.

The main problem (as you have probably already guessed) is that I cannot validate the cells that have vlookup formulas in them. An example of what I need to achieve is that the user selects 11 items using the validation lists to populate column 2. Following this columns 3/4 and 5 will be poplulated by the vlookup formulas. It is possible that in one of these columns, that there could be four entries of data that are the same, but the user is only allowed three. What I would like to do is prevent the fourth item being entered.

I am able to do this with a rather lengthy IF, OR formula, however I would like to create a pop up for the user rather than a line of text on the sheet. I'm very new to VBA, which I suspect is what is required here, therefore if anyone can provide any help/guidance here, it would be very much appreciated.
Many Thanks
Lee

Dave Hawley · Aug 2nd 2004

Hi Lee

Welcome to ozgrid.com

Can't you use an IF formula like
=IF(VLOOKUP(A1,MyTable,4,FALSE)="Dog","",VLOOKUP(A1,MyTable,4,FALSE))

Some VBA could be written but I need to know which Columns should be checked and which words should not be returned as the VLOOKUP Result? Also, other than a message box, what should happen to result in the cell housing the VLOOKUP? I'm thinking it's referenced cell (A1 in my example) should be undone?

Lee(UK) · Aug 2nd 2004

Hi Dave,
Thanks for your reply.

I'm not sure that I've explained myself correctly as I don't think your solution is what I'm after. I've attached the file I'm working on. As you can see the user selects an item in column C from a drop down list that is validated by a list supplied by the other sheets in the workbook. Columns D, E and F are populated via vlookup formulas after the user has selected an item in column C.

The problem I'm having is that I need some validation for columns D and E. The normal validation function doesn't work as the result is being created by a formula. For column D I need to ensure that there are no duplicates - if a duplicate occurs I'd like a msgbox to alert the user to the error and prevent the input (the same as if you'd used a formula to prevent duplicates upon input). For column E the I need to validate if there are more than three entries the same - i.e you can select 3 players from the same club but not more than this - again the alert should be similar to column D.

Hope this gives you a better idea of what I'm trying to do. Apologies for being a bit vague in the first post.
Cheers
Lee.

jindon · Aug 2nd 2004

Hi,

I'm not sure if the attached is what you desire.

Lee(UK) · Aug 2nd 2004

Hi Jindon
Yes, that is pretty much want I want. Thank you very much for you help. Could I ask one further favour, as I'm just learning vba, I can get a general idea of what's going on, however, could you just give me a brief explanation or put some comments into the code so I get a better idea. If you could it would be very much appreciated.
Cheers
Lee.

jindon · Aug 2nd 2004

Hi,

Here's how it works

Code

Private Sub Worksheet_Change(ByVal Target As Range)
'trigger to run a macro
With Target
    If .Count <> 1 Then Exit Sub
    'if number of cells selected is not 1, then do nothing and end macro
    If .Column <> 3 And .Row < 15 And .Row > 25 Or .Value = "" Then Exit Sub
    'if activecell is not in col.C, between row 15 to 25, then do nothing and end macro
        If WorksheetFunction.CountIf(Range("d15:d25"), .Offset(, 1).Value) > 1 Then
        'if countif(D15:D25,corresponding value in col.D to active cell) is greater than 1,
            MsgBox "Invalid Code"
            'Message Box stating "Invalid Code"
            .ClearContents
            'clear the active cell value
            .Select
            'activate the cell again
            Exit Sub
        ElseIf WorksheetFunction.CountIf(Range("e15:e25"), .Offset(, 2).Value) > 3 Then
            MsgBox "Invalid Club"
            .ClearContents
            .Select
            Exit Sub
        End If
End With


End Sub

Display More

Lee(UK) · Aug 2nd 2004

Jindon,
Thanks very much once again. Works a treat.
Cheers
Lee.

Validation query

Participate now!

Share