Dynamic/dependent Dropdown inline tables (de-normalized source)

[TurnOverExcel]

Dear MrExcel-Community,
once again an dependent dropdown question, but I haven't seen a solution (no VBA) for my problem.
I've seen a couple of approaches putting values into separate ranges and refering then with INDIRET in data validation, but vor some reason I require the format you see in tbl_Threat.

I have a source-table (tbl_Threat) and an risk-analysis table (tbl_Analysis). tbl_Threat contains denormalized data whcih should be the input for dependent dropdowns in tbl_Analysis.

I use a helper cell (named range "DD_Cluster" [I]=[I]UNIQUE(FILTER(tbl[/I]_Threat[Cluster];tbl_Threat[Cluster]<>""))[/I]) as input in data validation for tbl_analysis[Cluster] which works fine for initial selection:

• for the next columns in tbl_Analysis I want to select filtered/uniquie entries from tbl_Threat[Threat] where tbl_Threat[Cluster] equals to selected value in tbl_Analysis[Threat]. e.g. for a selected Force majeure the dropdown in the same row of tbl_Analysis[Threat] should be the data validation dropdown should contain
  
  .
  
  
  
• Well I tried the formula
  [I]=UNIQUE(FILTER(INDIRECT("tbl_Threat[Threat]");OFFSET(INDIRECT("tbl_threat[THREAT]");0;-1)=INDIRECT("tbl_Analysis[@Cluster]")))[/I]
  which returns the right results outside the table (see green colored rows (formula in cells K7 and M4 as example), but I need that as data-validation in each row of tbl_Analysis[Threat]
  
  
  
  ---> when I try to put that formula into data valiation (even with an ending #) I get an error
  ---> I tried to put that as helper named range (same as I did with DD_Cluster) but how to refer then to the current row in tbl_Analysis (without vba)
  ===> I have no clue how to; please help
  
  
• I want to the than the same with tbl_Analysis[Measures] based on the selection in tbl_Analysis[Threat] s. above

The excel workbook ("BCM__Dropdown_Challenge.xlsx") you can access here.

Thanks in advance!

 

[TurnOverExcel]

Note: Please don't wonder about the ";" instead of komma "," in the formulas. This is because of my regional settings.

 

[dbro34]

Dear MrExcel-Community,
once again an dependent dropdown question, but I haven't seen a solution (no VBA) for my problem.
I've seen a couple of approaches putting values into separate ranges and refering then with INDIRET in data validation, but vor some reason I require the format you see in tbl_Threat.

I have a source-table (tbl_Threat) and an risk-analysis table (tbl_Analysis). tbl_Threat contains denormalized data whcih should be the input for dependent dropdowns in tbl_Analysis.
View attachment 63620 View attachment 63621

I use a helper cell (named range "DD_Cluster" [I]=[I]UNIQUE(FILTER(tbl[/I]_Threat[Cluster];tbl_Threat[Cluster]<>""))[/I]) as input in data validation for tbl_analysis[Cluster] which works fine for initial selection:
View attachment 63622

• for the next columns in tbl_Analysis I want to select filtered/uniquie entries from tbl_Threat[Threat] where tbl_Threat[Cluster] equals to selected value in tbl_Analysis[Threat]. e.g. for a selected Force majeure the dropdown in the same row of tbl_Analysis[Threat] should be the data validation dropdown should contain View attachment 63623.
  
  
  
• Well I tried the formula
  [I]=UNIQUE(FILTER(INDIRECT("tbl_Threat[Threat]");OFFSET(INDIRECT("tbl_threat[THREAT]");0;-1)=INDIRECT("tbl_Analysis[@Cluster]")))[/I]
  which returns the right results outside the table (see green colored rows (formula in cells K7 and M4 as example), but I need that as data-validation in each row of tbl_Analysis[Threat]
  View attachment 63624
  ---> when I try to put that formula into data valiation (even with an ending #) I get an error
  ---> I tried to put that as helper named range (same as I did with DD_Cluster) but how to refer then to the current row in tbl_Analysis (without vba)
  ===> I have no clue how to; please help
  
  
• I want to the than the same with tbl_Analysis[Measures] based on the selection in tbl_Analysis[Threat] s. above

The excel workbook ("BCM__Dropdown_Challenge.xlsx") you can access here.

Thanks in advance!

Did you find a solution for this as I have the same need? Thanks!