+ Reply to Thread
Results 1 to 5 of 5

Custom data validation with formula doesn't work & acts as if it was "any value"

  1. #1
    Registered User
    Join Date
    02-04-2021
    Location
    Tricity
    MS-Off Ver
    Excel 2008
    Posts
    6

    Unhappy Custom data validation with formula doesn't work & acts as if it was "any value"

    Hi all,

    first post, so greetings & thanks for having me here. Run into a problem when I tried to set up a custom data validation in one of my spreasheets (attaching sample).

    "Week No" column is formula based, so values in it should be: =WEEKNUM(A2;13). Column A is where I have my dates. Now when I try to set up data validation, so it's the same as the formula in the cell, i.e. =WEEKNUM(A2;13) it seems not to work. I can click on a cell and type in anything I want to. Can't you help with this issue? I used this logic before on other sheets and it worked.

    Thanks,
    M.
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    Re: Custom data validation with formula doesn't work & acts as if it was "any value"

    When using formula validation, you should construct formula in a way that returns true/false.

    Ex:
    =WEEKNUM(A2,13)=C2
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    02-04-2021
    Location
    Tricity
    MS-Off Ver
    Excel 2008
    Posts
    6

    Re: Custom data validation with formula doesn't work & acts as if it was "any value"

    Worked like a charm. Thank you!

  4. #4
    Registered User
    Join Date
    02-04-2021
    Location
    Tricity
    MS-Off Ver
    Excel 2008
    Posts
    6

    Re: Custom data validation with formula doesn't work & acts as if it was "any value"

    One more thing: even if I have a data validation rule on a cell that's based on a formula I can still hit "delete" or "backspace" and erase the formula. Is there a way to prevent that from happening other than locking cells?

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    Re: Custom data validation with formula doesn't work & acts as if it was "any value"

    You are welcome

    If you are satisfied with solution provided to your initial question. Please mark the thread as solved, using thread tools found at top of your initial post.

    Is there a way to prevent that from happening other than locking cells?
    There isn't unfortunately.

    Excel isn't meant for rigid data entry validation, and built for flexibility.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 11-19-2020, 08:30 PM
  2. [SOLVED] Rank function using "array IF" formula as ref doesn't seem to work
    By vizzkid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2014, 09:47 AM
  3. [SOLVED] "Find" doesn't work when cell has formula in it
    By Xx7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2014, 10:19 PM
  4. Open files from FTP server (works with "servername" but doesn't work with "ip address")
    By adammsu1983 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-24-2012, 04:30 PM
  5. vba doesn't work with "iferror()" and formula autofill problem
    By lkim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-10-2012, 07:47 AM
  6. Replies: 2
    Last Post: 06-09-2006, 10:20 PM
  7. [SOLVED] Cell Validation "Ingnore Blanks" doesn't work
    By keithb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2005, 09:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1