$15 USD: Data Validation formulas to restrict data being entered in a cell

  • I am looking for some Data Validation formulas to prevent users to enter a data in a cell if there is a value present in another cell. I'm not sure if this can be done by formulas or VBA. I attached the file for reference. Here is the criteria:


    Data Validation criteria




    • Do not want users to be able to input data in both Col E and Col F. Only a value allowed in one of the 2 columns per row (Option 1 or Option 2)
    • So if a value is present in Col E and someone tries to input a value in Col F in the same row, I would a like a pop-up message to say "Invalid Entry: Please remove amount from Column F to enter %"
    • So if a value is present in Col F and someone tries to input a value in Col E in the same row, I would a like a pop-up message to say "Invalid Entry: Please remove amount from Column E to enter Amount"


    Thank you!


    10% has been sent via PayPal

  • Re: $15 USD: Data Validation formulas to restrict data being entered in a cell


    Hello


    I could do this for you.
    Do you want a VBA solution?


    Wim

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: $15 USD: Data Validation formulas to restrict data being entered in a cell


    Okay, let me work on this one.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: $15 USD: Data Validation formulas to restrict data being entered in a cell


    Hello


    I just finished coding and testing. I will send my PayPal details and upon receiving the payment of 90%, I will post up the solution for testing.
    If anyone is still unclear or incorrect, we will solve this immediately.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: $15 USD: Data Validation formulas to restrict data being entered in a cell


    Sam: you should hgit F5 in the browser :-)
    Hope you did not do useless work.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: $15 USD: Data Validation formulas to restrict data being entered in a cell


    Here's my code, it should go in the code behind the sheet where you want the event to be active.


    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: $15 USD: Data Validation formulas to restrict data being entered in a cell


    You're welcome :-)

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: $15 USD: Data Validation formulas to restrict data being entered in a cell


    Wigi...could you help me with one more thing with this file? Hopefully something really simple. Could you adjust the code you gave me to fit my latest file (see attached). There are more columns and I have some existing Worksheet change code in the file (see below) that I would like to still use. Would it be possible to integrate this with the code you provide? The columns I want the Data Validation code to work on now BA and BB.


    Thanks!



    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, Range("c2")) Is Nothing Then Exit Sub Else Range("a6:a35").AutoFilter 1, "<>0", , , 0
    2. End Sub
  • Re: $15 USD: Data Validation formulas to restrict data being entered in a cell


    Hello


    Try:


    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --