Dynamic & Dependent Data Validation List Based on Data Table

[henrynick]

Hello all. After being a faithful fan of this forum and constantly reading all of the helpful advice by this community, I have decided to register and post my first ever question.

I am a 2010 Excel user and consider myself average (maybe even below average) with the program and I am currently trying to grasp VBA so any advice would be appreciated.

I offer my apologies for such a long post, but would like to give as much information as possible; so here goes...


I am putting together a spreadsheet used to calculate overall weights of equipment. This equipment includes pipe, valves, instruments, ect. I have made a table (naming it WTDATA) in Excel with 7 columns.

Column headers “A” to “G” in table WTDATA are as follows:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD="align: center"]CATEGORY
[/TD]
[TD="align: center"]COMPONENT
[/TD]
[TD="align: center"]DESCRIPTION
[/TD]
[TD="align: center"]FACTOR
[/TD]
[TD="align: center"]DRY_WT
[/TD]
[TD="align: center"]OP_WT
[/TD]
[TD="align: center"]HYD_WT
[/TD]
[/TR]
[TR]
[TD="align: center"]---
[/TD]
[TD="align: center"]---
[/TD]
[TD="align: center"]---
[/TD]
[TD="align: center"]---
[/TD]
[TD="align: center"]---
[/TD]
[TD="align: center"]---
[/TD]
[TD="align: center"]---
[/TD]
[/TR]
</tbody>[/TABLE]

On other worksheets, I would like to use the information in this table to make dependent & dynamic drop down lists. In the other worksheet, the user would select the Category in column A. Column B would be a filtered (depndent of category) list of Components, and column C would be a filtered (dependent of components) list of Description.

NOTE: There are several duplicates in column A (Category).

As of now, when I update the table, I manually update named ranges on another worksheet and use =INDIRECT for these data validation list. Once the three items mentioned are picked from the lists, I use an INDEX/MATCH formula to pull weight information from the table.

The goal is to let others use this file to continue building the table with more information over time without making them manually update an additonal sheet of named ranges. As the table is updated, the data validation lists also become updated.

I have tried experimenting with =IFERROR(INDEX(WTDATA[CAT],1),"") and =IFERROR(INDEX(WTDATA[CAT],MATCH(0,COUNTIF(L$2:L2,WTDATA[CAT]),0)),"") to pull unique values from the table. I've also tried filtering the columns in a new worksheet and using a Pivot Table. I would prefer generating the data validation source straight from the table however.

Is there more automated way to extract unique values from the table, WTDATA's, first 3 columns and use them for dependent data validation lists?

 

[henrynick]

Any Ideas?

ray: I would be very gratefule if anyone would have a good/better method for keeping a running set of compiled data while using part of it to generate dynamic-dependent validation lists which also filters out duplicates.

 

[Akashwani]

Hi and welcome to MrExcel.

Check these links out....

Excel Data Validation Introduction

Excel Names -- Excel Named Ranges

Excel Magic Trick # 259: Dynamic DV List Based On DV List - YouTube

Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula | Get Digital Help - Microsoft Excel resource

I hope these help and good luck with your project.

Ak