Forum Discussion
Data Validation with unique values
Hi,
I have a question on Data Valitation.
I have a list with duplicated values on it. I do data validation with this list to get a drop list. When done with one computer I get a drop list with only unique values out of that list (that is what I want), but if I do the same - same file - in another computer, I get a drop-list with duplicated values (many lines with same text). Do you know what the reason would be? Thanks
Alvaro
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- NikolinoDEGold Contributor
The behavior you are describing with Data Validation drop-down lists can occur due to the settings applied on different computers or the specific version of Excel being used. Here are some factors to consider to ensure that you get unique values in the drop-down list on all computers:
- Data Validation Settings:
- Ensure that the Data Validation settings are consistent on both computers. The settings should be configured to allow only unique values if that is what you desire.
- List Source:
- Verify that the list source used for Data Validation is the same on both computers. If the source data contains duplicates, the drop-down list will reflect those duplicates.
- Excel Version:
- Different versions of Excel may handle Data Validation differently. Ensure that both computers are using the same Excel version, and if not, check for any version-specific behavior that might affect Data Validation.
- List Range:
- Double-check the range specified for Data Validation. If it includes extra rows or duplicates on one computer, it can result in different drop-down lists.
- Named Ranges:
- If you are using named ranges for Data Validation, verify that the named ranges are consistent and do not include duplicates.
- Workbook Compatibility Mode:
- Ensure that the workbook is not opened in Compatibility Mode, as this can affect how Data Validation works.
- Data Formatting:
- In some cases, formatting or styles applied to cells can affect how Data Validation displays lists. Make sure cell formatting is consistent.
- Excel Add-ins or Extensions:
- Check if there are any Excel add-ins or extensions installed on one computer that might be altering Data Validation behavior.
- Excel Options:
- Compare the Excel options and settings between the two computers, paying special attention to any settings related to data or lists.
- Office Updates:
- Ensure that both computers have the latest updates and patches for Microsoft Office. Sometimes, updates can resolve compatibility issues.
By addressing these factors, you can help ensure that Data Validation drop-down lists display unique values consistently across different computers. If the issue persists, it may be helpful to share specific details about the Data Validation settings and the Excel versions being used on both computers for more targeted assistance. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- Alvarom1Copper ContributorThanks, where do I see the settings to "allow only unique values"?