Validation query

  • 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

  • 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?

  • 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.

  • 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.

  • Hi,


    Here's how it works


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!