+ Reply to Thread
Results 1 to 13 of 13

Google Sheets: The data you entered in cell xx violates the data validation rules set on

  1. #1
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Google Sheets: The data you entered in cell xx violates the data validation rules set on

    I built a workbook in Excel then uploaded it to Google sheets. It includes cells with Data Validation using List with Dropdown. It works fine in Excel. In Google sheets, the dropdown shows the correct list of choices but when I select one I get the message "The data you entered in cell xx violates the data validation rules set on this cell." I have hundreds of cells with this DV rule and they all do the same thing.

    The list is a named range Personnel that is on sheet List, and there are several sheets that use this rule.

    I have not used Google sheets a lot so I am not sure how to share it for read-only to you, while allowing two other people to have full access. Here is the Excel version.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,336

    Re: Google Sheets: The data you entered in cell xx violates the data validation rules set

    Can't you copy it to a different location and share the copy here?

    I've imported the attachment into Google Sheets and get this for every drop-down:

    Invalid:
    Input must fall within specified range
    I don't even see a list.

    There is no sheet called List, just four monthly sheets.
    Last edited by AliGW; 05-01-2022 at 10:05 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Google Sheets: The data you entered in cell xx violates the data validation rules set

    It is because you are using an OFFSET function for the named range:

    Please Login or Register  to view this content.
    Google sheets doesn't like OFFSET in a named range.

    It would seem that your intention is to have a dynamic named range used for the dropdown.

    To create a dynamic named range.....

    * In a cell (B2 on Sheet2), count the rows in the range on Sheet1:
    Please Login or Register  to view this content.
    * In cell C2 on Sheet2, create a string that will define the range:
    Please Login or Register  to view this content.
    * Select cell C2 on Sheet2, and create a named range (in this example called dynamicRange)
    * When referencing the "Dynamic Named Range" call it with the INDIRECT function:
    INDIRECT(dynamicRange)

    BUT.... You wont be able to use this dynamic named range for the dropdown.

    Instead, just select a range longer than you think you will need (for example A2:A100), duplicates and blanks will be filtered out
    Last edited by janmorris; 05-01-2022 at 03:23 PM.
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  4. #4
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Google Sheets: The data you entered in cell xx violates the data validation rules set

    regarding sharing...

    set it to sharing anyone with the link can view...

    then for the specific people you want to give edit access, select editor access and enter in their email addresses.

    now only those who use their gmail account (that you have authorised) to access can edit... so, you can not share to some non-gmail managed account for editor access because google has no authority over those.

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Google Sheets: The data you entered in cell xx violates the data validation rules set

    https://docs.google.com/spreadsheets...it?usp=sharing

    @Ali, List was hidden, it is now visible

    I ran into the OFFSET problem right away so I just hardcoded the range. So that's not the problem.Capture1.JPG

    Capture2.JPG

  6. #6
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Google Sheets: The data you entered in cell xx violates the data validation rules set

    @6StringJazzer you can NOT use a dynamic named range for a dropdown.. Google Sheets doesn't like it

    See post #3 for the solution to your problem

    If not having a dynamic range, then just reset the dropdown (using the named range) on one cell, then copy it to all other locations.

    Without access to the file, theres little else i can troubleshoot
    Last edited by janmorris; 05-02-2022 at 01:32 AM.

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Google Sheets: The data you entered in cell xx violates the data validation rules set

    @janmorris see post #5. There is a link to the file, and I explain that the named range is static.

  8. #8
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Google Sheets: The data you entered in cell xx violates the data validation rules set

    in post #5, the file is locked from viewing/editing

    having a newly created static range for one dropdown is one thing... but did you copy the newly created dropdown to replace all the others?

    when i used the original XLSX file from post #1, converted it to google sheets, deleted the original named range, recreated a new range (same size or longer than needed), created new Data validation on 1 cell, and dragged it to other cells, it fixed the problems on those other cells i dragged it to.

    the point is, when you recreate the named range, it does appear that the options are available, but the issue is still present due to some kind or artifacts remaining from the original named range. you need to create the new dropdown, then drag it over the old ones. have you done that?

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

    Re: Google Sheets: The data you entered in cell xx violates the data validation rules set

    Here's how I would do it.

    In the Excel file. Change the named range to static. Then save and upload to Google Drive and replace existing.

    No need to edit. It should take the static named range as is. Below is the converted file using above method.

    https://docs.google.com/spreadsheets...f=true&sd=true
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  10. #10
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Google Sheets: The data you entered in cell xx violates the data validation rules set

    Quote Originally Posted by janmorris View Post
    having a newly created static range for one dropdown is one thing... but did you copy the newly created dropdown to replace all the others?
    I did not create a new range, I redefined the existing list Personnel. So the dropdowns did not have to be changed.

    you need to create the new dropdown, then drag it over the old ones. have you done that?
    Never occurred to me that would be necessary but let me try.

  11. #11
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Google Sheets: The data you entered in cell xx violates the data validation rules set

    If I re-save the data validation rule in Google (even without changing it) it works.

    Quote Originally Posted by CK76 View Post
    In the Excel file. Change the named range to static. Then save and upload to Google Drive and replace existing.
    That worked too, and was easier than updating all the rules in Google.

    So my conclusion is that if you define a DV rule using a named range, Google will not dynamically update the rule if the named range is redefined.

    Thanks to all for the help.

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

    Re: Google Sheets: The data you entered in cell xx violates the data validation rules set

    You ware welcome and thanks for the rep

    So my conclusion is that if you define a DV rule using a named range, Google will not dynamically update the rule if the named range is redefined.
    Yep. I tend not to use dynamic named range if I'm converting to Google sheets. More headache than what it's worth. If absolutely necessary, you could write App.Script to update named ranges on Google sheets.

  13. #13
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Google Sheets: The data you entered in cell xx violates the data validation rules set

    So my conclusion is that if you define a DV rule using a named range, Google will not dynamically update the rule if the named range is redefined.
    actually, if you update the range of a named range, it will update correctly.

    the issue here is that when an excel file is imported and converted, and the named range uses OFFSET, there are some kind of artifacts that screw with google sheets which then requires to remake the dropdowns.

    the way i deal with that is to just use standard ranges that are longer than necessary and then ignore blanks... these pose no problem.

    thanks for the reps too 6string, much appreciated.

+ 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. Google Sheets: Problem with Data Validation Formula
    By Paige W in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 11
    Last Post: 01-10-2022, 11:10 AM
  2. [SOLVED] multiple data validation rules for one cell
    By UWBadger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-24-2021, 11:48 AM
  3. Replies: 1
    Last Post: 09-19-2018, 06:15 PM
  4. [SOLVED] Two Data Validation Rules In One Cell
    By Bosulli in forum Excel General
    Replies: 3
    Last Post: 03-08-2018, 12:53 PM
  5. Data validation and formula - Google Sheets
    By Human2014 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 8
    Last Post: 04-05-2017, 10:56 AM
  6. List the cells with data validation rules referring to other sheets/books.
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-07-2011, 11:22 AM
  7. validation rules not working when someone copy paste data on validation cell
    By jthakrar in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-17-2010, 03:36 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1