Dynamic Dependent "Data Validation"

[ManiThani]

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.

[Pepe Le Mokko]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

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

[ManiThani]

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

[southward]

Hi,

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

First is a dynamic named range "Category":

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

[ManiThani]

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.

[southward]

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

[ManiThani]

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