3 Dimensional data vaildation without any duplicacy

[rakeshghuge]

HI....
I want to create a dynamic data validation sheet...
for insitance suppose i have a following database in excel...

<table style="border-collapse: collapse; width: 429pt;" width="573" border="0" cellpadding="0" cellspacing="0"><col style="width: 143pt;" span="3" width="191"> <tbody><tr style="height: 21.75pt;" height="29"> <td class="xl65" style="height: 21.75pt; width: 143pt;" width="191" height="29">RM Aggregate</td> <td class="xl65" style="border-left: medium none; width: 143pt;" width="191">RM Subaggregate</td> <td class="xl65" style="border-left: medium none; width: 143pt;" width="191">RM Grade</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">PLASTIC</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">POLYPROPYLENE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">PAR
</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">PLASTIC</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">POLYPROPYLENE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">PAS
</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">PLASTIC</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">POLYVINYL CLORIDE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">EXTRUSION</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">PLASTIC</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">POLYVINYL CLORIDE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Q70</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">PLASTIC</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">POLYVINYL CLORIDE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Q02</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">PLASTIC</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">POLYVINYL CLORIDE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">HNPV</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">PLASTIC</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">POLYETHYLENE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">HDPE, BLOW MOLDING</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">PLASTIC</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">POLYETHYLENE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">HDPE, INJECTION MOLDING</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">PLASTIC</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">POLYETHYLENE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">LDPE</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">PLASTIC</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">POLYSTYRENE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">NA</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">STEEL</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">STEEL SHEET</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">CRC-D</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">STEEL</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">STEEL SHEET</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">CRC-DD</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">STEEL</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">STEEL SHEET</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">CRC-EDD</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">STEEL</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">STEEL SHEET</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">HRC-D</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">STEEL</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">STEEL TUBE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">CEW 21- ANNEALED</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">STEEL</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">STEEL TUBE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">CEW 21</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">STEEL</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">STEEL WIRE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">CDLC</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">STEEL</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">STEEL WIRE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">CHQ</td> </tr> <tr style="height: 21.75pt;" height="29"> <td class="xl66" style="border-top: medium none; height: 21.75pt;" height="29">STEEL</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">STEEL WIRE</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">35A</td> </tr> </tbody></table>


now i have a huge list of Raw material in the database...
i want to create a series of datavalidation such that....if i select plastic in the first datavalidation then in the next data validation will only show a dropdown of the item which are infront of plastic in the above table....


like in this example...the first dropdown should show plastic & steel.....if i have selected plastic...the next dropdown list should show POLYPROPYLENE, POLYVINYL CLORIDE, POLYETHYLENE, POLYSTYRENE...and if i select POLYVINYL CLORIDE...the next dropdown should show EXTRUSION, Q70, Q02 & HNPV....

 

[ColinKJ]

Hi rakeshghuge,

I'll have a go at this and get back to you.

ColinKJ

 

[ColinKJ]

Hi rakeshghuge,

I've put together a test WB if you want to take a look, you can download it from :

http://www.box.net/shared/yyuzgkly1r

If you click into Column A, you get the opportunity to select from the two primary categories, If you then click in Column B on the same Row you only get those secondary categories associated with the Produce in column A. Likewise when you click in Column C.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
If you try to enter an incorrect input it should be cleared.<o></o>
<o></o>
Give it a try, and let me know how you get on.
<o></o>
ColinKJ

 

[rakeshghuge]

Hi ColinKJ,

You are the best.....yes this is what i was asking for.....

But the data which which i have posted will be as a database, in which NEW data can be added...

& the drop down should come in other excel sheet.....

at present if I add a new data it gives an error....


there should be a provision to add New data in the database...but the sheet in which the dropdown is provided should show only content which is in database...

is it possible?????

 

[ColinKJ]

rakeshghuge,

What format does your database come in, and often do you add new items to the database.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
If you look on the sheet in Columns AE : AN, you will see where the data is stored.<o></o>
<o></o>
Clearly, the number of columns will increase as the number of primary, secondary, etc product categories increase.<o></o>
<o></o>
Are you able to copy and paste an example of your database into your next post, or upload an example to a file share website from where I could download it

ColinKJ

 

[rakeshghuge]

PLEASE CHECK THE LINK... I HAVE UPLOADED A FILE...

http://www.esnips.com/doc/662a263f-b585-4228-aa5a-1cc393f690db/3-D-DATAVALIDATION

NOW IN THE SHEET 1 i.e. DATABASE THE DATA IS STORED....THIS DATA IS UPDATED ON A TIME TO TIME.

IN SHEET 2 i.e. RM DETAILS THE DROPDOWN SHOULD COME....IF A NEW ROW IS ADDED...ACCORDINGLY THE DROPDOWN SHOULD GET UPDATED IN RM DETAILS SHEET....

 

[ColinKJ]

rakeshghuge

I've downloaded it.

I'll have a look as soon as I can. I'm away for the weekend so it won't be until next week now.

I'll get back to you.

Regards

ColinKJ

 

[rakeshghuge]

thank u very much....


ur input has been very valuable.....

 

[ColinKJ]

Hi rakeshghuge,

I've done some work on your DB, you can download my test WB "3_D_DATAVALIDATION" from:

http://www.box.net/shared/yyuzgkly1r

The RM DETAILS sheet should operate as you want it.

When you add a new item on the DATABASE sheet, if you use the Ctrl+q combination, the drop down lists will be updated. You will need to remember to save the WB when you've added an new items to the DATABASE sheet.

Regards

ColinKJ