Dynamic Dependent "Data Validation -List"

Hi All,

Please help me to create a Data Validation list to Auto refresh from Master List.

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

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) -- attached the excel.

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

Thank you sir. The above is what I am looking for.

but I am facing issue while recreate the above steps in my excel. I am struggling with creating "Confirmed as array" step to List down field names.

1.Put the formula in List tab: B2, i.e. ==IFERROR(INDEX(mstField,MATCH(0,COUNTIF(B$1:B1,mstField)+NOT((mstCategory=B$1)),0)),"")
2.Select the range B2:D10
3.Press F2 key
4.hold down CTRL and SHIFT keys and press ENTER
I am getting the data i.e field name "D1" copied into all B2: D10 range where the formula holds to refer $B$1 cell everywhere.
Array is not considered , which means braces are not coming {}. Kindly help. Thank you

Thank you all. the below steps worked fine for me.

1. Copy the formula and put in one cell B2 (my sheet)
2. Press F2.
3.Press CTRL SHIFT ENTER
4. drag the formula everywhere the range you need.

My sincere thanks to the forum members user "CK76". I prepared my client worksheet based his formula and guidance. Its amazing and works perfectly. Thank you very much.