+ Reply to Thread
Results 1 to 9 of 9

Data Validation, problem after excel restart

  1. 10-07-2020, 08:25 AM #1
    gljiva
    gljiva is offline
    Registered User
    Join Date
    10-07-2020
    Location
    Croatia
    MS-Off Ver
    365
    Posts
    5

    Data Validation, problem after excel restart

    In data validation I use formula (example)
    = COUNT(FIND(List;A1))=0 or
    = COUNT(FIND(B1:B9;A1))=0
    to prevent the entry of special characters or space specified in the list (B1:B9). All cells are formatted as text

    I set the formula in Data Validation, stretch it to about A:10, save it and everything works. Reports errors for all characters typed in A1:A10 listed in B1:B9

    Then I turn off excel, turn it on again and the formula recognizes only the character in B1 and reports an error for it. For the others (B2:B9) after starting and turning off excel formula doesn't work.

    What i did wrong?
    Reply With QuoteRegister To Reply
  2. 10-07-2020, 10:16 AM #2
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Data Validation, problem after excel restart

    Can you upload an example workbook showing how you are setting up and using those formulas in Data Validation? See Yellow banner for directions.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 10-07-2020, 10:38 AM #3
    gljiva
    gljiva is offline
    Registered User
    Join Date
    10-07-2020
    Location
    Croatia
    MS-Off Ver
    365
    Posts
    5

    Re: Data Validation, problem after excel restart

    you can now see that the formula in the yellow box only recognizes the first one from the green list. open data validation delete the last zero in the formula, rewrite it, confirm it and then the formula recognizes everything from the green list. but after saving and reopening it recognizes only the first one from the green list.
    Attached Files Attached Files
    Last edited by gljiva; 10-07-2020 at 10:47 AM.
    Reply With QuoteRegister To Reply
  4. 10-09-2020, 06:08 AM #4
    gljiva
    gljiva is offline
    Registered User
    Join Date
    10-07-2020
    Location
    Croatia
    MS-Off Ver
    365
    Posts
    5

    Re: Data Validation, problem after excel restart

    has anyone idea how to solve this problem?
    Reply With QuoteRegister To Reply
  5. 10-09-2020, 07:07 AM #5
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,585

    Re: Data Validation, problem after excel restart

    Change this formula

    = COUNT(FIND(B1:B9;A1))=0

    as

    = COUNT(FIND($B$1:$B$9;A1))=0

    In the file you have changed the reffered cells. Formula in File is

    =COUNT(FIND($E$3:$E$9,C7))=0
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 10-09-2020 at 07:10 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
  6. 10-09-2020, 07:27 AM #6
    gljiva
    gljiva is offline
    Registered User
    Join Date
    10-07-2020
    Location
    Croatia
    MS-Off Ver
    365
    Posts
    5

    Re: Data Validation, problem after excel restart

    nope. same after reopening.
    Reply With QuoteRegister To Reply
  7. 10-09-2020, 08:30 AM #7
    Frandz
    Frandz is offline
    Registered User
    Join Date
    09-24-2020
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    4

    Re: Data Validation, problem after excel restart

    Turn off ignore blank in data validation.

    Not sure why this works, but it did for me.
    Reply With QuoteRegister To Reply
  8. 10-09-2020, 08:41 AM #8
    gljiva
    gljiva is offline
    Registered User
    Join Date
    10-07-2020
    Location
    Croatia
    MS-Off Ver
    365
    Posts
    5

    Re: Data Validation, problem after excel restart

    it works after you change it. but again, after reopening - nope
    Reply With QuoteRegister To Reply
  9. 10-09-2020, 10:38 AM #9
    Frandz
    Frandz is offline
    Registered User
    Join Date
    09-24-2020
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    4

    Re: Data Validation, problem after excel restart

    Ah, I'm sorry. I hope someone else can help you with this.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Unexpected Error - something Went Wrong, If the problem continues restart excel
    By excel_googler in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-27-2020, 04:12 PM
  2. [SOLVED] Macro Causing Excel to Restart
    By JBaum2000 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2020, 02:35 PM
  3. Excel workbook wont restart
    By RinorM in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-28-2014, 09:39 AM
  4. file error: data might be loss and Recover mywork and restart the microsft excel
    By celticpucca in forum Excel General
    Replies: 3
    Last Post: 06-14-2011, 04:46 AM
  5. Data Validation problem in EXCEL
    By vishusecl in forum Excel General
    Replies: 4
    Last Post: 12-21-2009, 11:47 AM
  6. Tricky problem in Data validation - Excel 2003
    By smadhuranath in forum Excel General
    Replies: 1
    Last Post: 07-18-2006, 04:15 AM
  7. [SOLVED] How to de-install a new update to restart excel
    By Sanjay Ghoshal in forum Excel General
    Replies: 0
    Last Post: 07-18-2006, 02:55 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1