Dynamic Dependent "Data Validation"

I have Master sheet in which 2 Columns like 1. Category and 2. Field Name. ("Master "data)

Category Field Name Updated
DEP-ACCT D1 05-Jan
DEP-ACCT D2 05-Jan
DEP-ACCT D3 05-Jan
TIME-DEP T1 05-Jan
TIME-DEP T2 05-Jan
TIME-DEP T3 05-Jan
DEP-TXN X1 05-Jan
DEP-TXN X2 05-Jan
DEP-ACCT D4 09-Jan
DEP-TXN X3 12-Jan
TIME-DEP T4 15-Jan


I have another tab ("Client to Fill") which has more columns and the requirement is listed below:
It has Category, Field Name, Data
1. The Category has DEP-ACCT, TIME-DEP and DEP-TXN (i can use Data Validation - List and restrict to use these 3 values)
2. Field Name, we need to bring it from Master Data and we need to use Data Validation - List again.
But Limit the Fields Names to be shown in drop down with associated Category.
Similarly, The client will keep adding Field Names randomly against the 3 categories.
So this Field Name drop down list should show the category and respective Field Names to them "automatically" by including new / additional fields.


Like this: ("Client to Fill "sheet looks)


Category Field Name Data to Fill
DEP-ACCT List down (D1,D2, D3, D4) Abc..
TIME-DEP List down (T1,T2, T3, T4) XyZ..
TIME-DEP List down (T1,T2, T3, T4) N1234..
TIME-DEP List down (T1,T2, T3, T4) Vtq..
DEP-TXN List down (X1,X2, X3)
DEP-TXN List down (X1,X2, X3)
TIME-DEP List down (T1,T2, T3, T4)
DEP-ACCT
List down (D1,D2, D3, D4)

I attached the excel sheet to refer.

I can not ask my client to use PQ or VBA. Please share any formula as of Excel 2010 version. thank you.

My apologies. I was not aware of the complete rules and registered yesterday in the forums.

Here is the cross forum posted link:

https://www.mrexcel.com/forum/excel-...tion-list.html

Hi,

What I have come up with consists of a couple key formulas.

First is a dynamic named range "Category":

=OFFSET('Master Data'!$B$3,0,0,COUNTA('Master Data'!$B:$B)-1,1)

The other is formula for a "Data Validation List" in the "Client to Fill" sheet cell C3 that can be copied down:

=OFFSET(INDIRECT(CELL("address",INDEX(Category,MATCH(B3,Category,0),1))),0,1,COUNTIF(Category,B3),1)

In order for this process to work the "Master Data" array has to be Filtered and Sorted by Category.

Attached is a working sample of the file you provided.

Hope this makes sense and works for you.

Cheers

I am sorry, this OFFSET formula i tried. But i can not ask my client to update the Master sheet to order or refresh it.

We can create another "Excel Table" to pull Data from "Master" and any formula to refresh separately this new table within the spreadsheet. This could be our source Data to reflect in Client to fill tab.

So when Master data appended at the end, This new excel table will be refreshed and inturn reflects new data in the List down box.

OK. OFFSET formulas are good: Check

Not able to ask client to update Master sheet: Check. (Clients can be such a pain. lol)

Create mirror "Master Data" sheet to pull data from: Check

How about macros? Can we use macros?
I have modified the example spreadsheet to include a Pivot Table to pull data from the "Master Data" table dynamically.
However, Pivot Tables have to be refreshed in order be updated.
The Pivot Table will automatically keep the data in the Filter/Sorted format needed for the formula in the Data Validation List to return the right values.
Included is a simple macro that refreshes the Pivot Table whenever a change occurs in the "Master Data" sheet.
This way you don't have to ask the client to update the Pivot Table every time new data is added to the "Master Data" array.

This simple macro goes in the "Microsoft Excel Objects" "Sheet2(Master Data)" code area:

Private Sub Worksheet_Change(ByVal Target As Range)
   'If data on this worksheet changes, refresh the pivot table
   Sheets("MD Pivot").PivotTables("PivotTable1").RefreshTable

End Sub

I changed the "Category" dynamic name to look at the Pivot Table instead of the "Master Data" array:

=OFFSET('MD Pivot'!$A$4,0,0,COUNTA('MD Pivot'!$A:$A)-1,1)

I added another dynamic named range "MasterDataArray" for the Pivot Table to look at the "Master Data" array:

=OFFSET('Master Data'!$B$2,0,0,COUNTA('Master Data'!$B:$B),3)

The Pivot Table has to look at the "Master Data" array via the "MasterDataArray" named range.
You can do this in the "Change Data Source" in the Pivot Table sheet. Set the "Change PivotTable Data Source to:

=MasterDataArray

I realize this has gotten rather complicated. However, it does work.

Attached is the modified version of your example.

Test it out. Add new data to the "Master Data" array. See that the MD Pivot table updates and new Field Names are available in the pull downs.
Remember, this is not a macro enabled spreadsheet.

Let me know how it works for you and if changes are needed.

Cheers

Thank you sir. I modified your excel worksheet to fit into my original requirement sheet and works fine. Thanks again.