prevent duplicate in Data Validation

  • Thread starter Thread starter wiwi
  • Start date Start date
W

wiwi

I have a book that contain two sheets (Costing & Accessories).
My Problem are
1.how to hide the duplicate data in Data Validation ?
In Costing sheet, in column B18:B31 use Data-Validation->List->Sourc
=AccCategory (range name for Accessories sheet column category)
Because Accessories sheet is a table data then may have a duplicat
data in Category field. How to hide the duplicate data and only sho
single data when in Data Validation ?

2. filter selection into another combobox
what I'm select in B18 then will filterlist into C18. e.g in B18 i
"Drawcord" then in C18 only show "Drawcord" data for user to select
When user select the data, all the other field like curr,qty,supplie
will get the data from the selected record of C18

Please advice and hel

Attachment filename: book3.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=40558
 
You can't hide duplicate values if they're in a table
used for data validation I'm afraid.

You need to use the advanced filter to extract unique
items to another area.
An alternative would be to use a scripting.dictionary and
load it with unique items. This works like a collection,
but it has an Exists property
If a key exists already the property is true. If its
false, then you can add it to the dictionary.

What you want can be done.
If you load a recordset with the data in memory. when you
select an item in the first dropdown (B18) use this value
as a filter for the recordset and drop the recordset into
as table on another sheet. you can create a new
dictionary for unique items, and then use a VLOOKUP for
the data.


HTh
Patrick Molloy
Microsoft Excel MVP
 
Back
Top