# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  > [SOLVED] Google Sheets: The data you entered in cell xx violates the data validation rules set on

## 6StringJazzer

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.

----------


## AliGW

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.

----------


## janmorris

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  :Smilie:

----------


## janmorris

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.

----------


## 6StringJazzer

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

----------


## janmorris

@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

----------


## 6StringJazzer

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

----------


## janmorris

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?

----------


## CK76

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

----------


## 6StringJazzer

> 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.

----------


## 6StringJazzer

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





> 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.

----------


## CK76

You ware welcome and thanks for the rep  :Smilie: 





> 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.

----------


## janmorris

> 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.  :Wink: 

thanks for the reps too 6string, much appreciated.

----------

