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

TurnOverExcel

New Member
Joined
May 1, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
tbl_Threat.png
tbl_Analysis.png


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:
DD_Cluster.png


  • 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
    Sample__Force_majeure.png
    .


  • 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]
    Try_Formula.png

    ---> 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!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

TurnOverExcel

New Member
Joined
May 1, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Note: Please don't wonder about the ";" instead of komma "," in the formulas. This is because of my regional settings.
 

Forum statistics

Threads
1,176,670
Messages
5,904,378
Members
435,088
Latest member
Kingsof82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top