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
--
Message posted from http://www.ExcelForum.com

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




-----Original Message-----
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-Source
=AccCategory (range name for Accessories sheet column
category).
Because Accessories sheet is a table data then may have
a duplicate
data in Category field. How to hide the duplicate data
and only show
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 is
"Drawcord" then in C18 only show "Drawcord" data for
user to select.
When user select the data, all the other field like
curr,qty,supplier
will get the data from the selected record of C18

Please advice and help

Attachment filename:
book3.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=405585
---
Message posted from http://www.ExcelForum.com/

.

1. There's a sample file here that uses formulas to extract
unique items from a list:

http://www.contextures.com/excelfiles.html

Under Functions, look for 'Extract Items with Formulas'

2. There are instructions here for creating dependent data
validation lists:

http://www.contextures.com/xlDataVal02.html

wiwi < wrote:
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-Source
=AccCategory (range name for Accessories sheet column category).
Because Accessories sheet is a table data then may have a duplicate
data in Category field. How to hide the duplicate data and only show
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 is
"Drawcord" then in C18 only show "Drawcord" data for user to select.
When user select the data, all the other field like curr,qty,supplier
will get the data from the selected record of C18


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html