Results 1 to 4 of 4

Thread: Help with Data Validation

  1. #1
    Neophyte Kiwi den's Avatar
    Join Date
    Mar 2016
    Location
    Napier NZ
    Posts
    3
    Articles
    0
    Excel Version
    Office 365

    Help with Data Validation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a spreadsheet and has been working fine up until a week ago.
    Some cells have data validation conditions in them to restrict the valve depending on a maximum length entered
    For some reason this is now not working and I can enter a number in the cell that I know is under the restrictions and I still get error message

    any thoughts

    File is attached

    Any help appreciated
    Attached Files Attached Files

  2. #2
    Seeker jono's Avatar
    Join Date
    Dec 2015
    Posts
    13
    Articles
    0
    Excel Version
    2016
    Hi,

    I had a quick look by coping the data validation formula in to the cell directly to see what the max value would be and I saw that the Z335 was reporting the max value for the last entry on the lookup table, so it seems it wasn't able to locate the lookup value.

    I added ",FALSE" to the end of the validation so that it located an exact match, which seemed to work in my quick tests.

    =VLOOKUP(A31,Aluminium!$A:$D,4,FALSE)

    I didn't look in to why it was not working without it.

    Hope it helps

    Jon

  3. #3
    Neophyte Kiwi den's Avatar
    Join Date
    Mar 2016
    Location
    Napier NZ
    Posts
    3
    Articles
    0
    Excel Version
    Office 365
    Cheers Jon,, works perfectly

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,711
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    I didn't look in to why it was not working without it.
    Without FALSE or 0 at the end, VLOOKUP assumes (defaults to) TRUE or 1, which is NOT an exact match (it's an approximate match). It's always best to add the lookup type argument.
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •