Forum Discussion
DATA VALIDATION WITHOUT DUPLICATES, BLANKS
Hello,
I'm attempting to create an interactive dashboard yet I haven't learned how to get the Data Validation functions to ignore duplicate text values in column E for a drop down menu of suppliers. Ultimately, I'm trying to get Excel to match the values in column A and match those against consumption volumes found in a different column of the same worksheet.
Also, has Microsoft released an instruction book for Excel 365?
Thank you.
1 Reply
To create a dropdown menu for suppliers in Excel using Data Validation while ignoring duplicates and blanks, you can utilize a dynamic range combined with a formula to extract unique values from column E. Here is how you can do it:
- Create a Named Range for Unique Suppliers:
- Select the cell where you want your dropdown menu to appear.
- Go to the Formulas tab > Name Manager (or press Ctrl + F3).
- Click on "New" and enter a name for your range (e.g., "UniqueSuppliers").
- In the "Refers to" field, enter the following formula:
=UNIQUE(FILTER(E:E, E:E<>""))
- Click OK to save the Named Range.
- Apply Data Validation:
- Select the cell where you want the dropdown menu.
- Go to the Data tab > Data Validation.
- In the Settings tab, choose "List" from the Allow dropdown.
- In the Source field, enter:
=UniqueSuppliers
- Click OK to apply Data Validation.
This setup will create a dropdown menu for suppliers in the selected cell, containing unique values from column E while ignoring duplicates and blanks.
Regarding your question about an instruction book for Excel 365, Microsoft often releases official documentation and guides for their products, including Excel 365. You can typically find these resources on the Microsoft website or through their official documentation portal. Additionally, click on the link. The text was created 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.
