Help creating drop-down validation list with specific criteria

Try something like this:

In spreadsheet b cell G2:
=IFERROR(INDEX('spreadsheet d'!B:B,AGGREGATE(15,6,ROW('spreadsheet d'!A$2:A$1100)/('spreadsheet d'!A$2:A$1100=C$2),ROWS($1:1))),"")
Drag the formula through G100

Then go to Formulas > Define Name > Name: DVlist > Refers to:
='spreadsheet b'!$G$2:INDEX('spreadsheet b'!$G$2:$G$100,SUMPRODUCT(--('spreadsheet b'!$G$2:$G$100<>"")))
OK

Now select cell D2 > Data > Data Validation > Allow: List > Source: =DVlist > OK

See attachment.

Thank you both for your help so far.

I'll attach my excel sheet to better describe what I'm after as requested, and then I'll try 63falcondude's recommendations and see if I can get it working.

I've had a look and this solution definitely works - however for Major Themes or as you reference them 'conditional values', there will be multiple and they will vary from project to project and throughout the duration of the project - I don't know that the users will be able to (or perhaps just too confusing for them) create separate DV lists for each new conditional value as they arise... also I'm not sure that the drop down list will automatically know which DV list to reference on the depending 'conditional value'

Try this:

Format your data in Sub Topics into a table. I gave the headers "Major Theme" and "Sub Topic".

D1 =IFERROR(INDEX(Table1[Major Theme],MATCH(0,COUNTIF($C1:C1,Table1[Major Theme]),0)),"") Ctrl Shift Enter
Drag to the right as far as needed (I went through M1 to allow for an additional 6 major themes).

D2 =IFERROR(INDEX($B:$B,SMALL(IF(Table1[Major Theme]=D$1,ROW(Table1[Major Theme])),ROWS($1:1))),"") Ctrl Shift Enter
Drag to the right as far as you dragged the previous formula then down as far as needed (I went through row 100 to allow for a total of 99 sub topics per major theme).

Now select cells D2:D1000 in the Grab Board worksheet > Data > Data Validation > Allow: List > Source:
=OFFSET('Sub Topics'!$D$1,1,MATCH($C2,'Sub Topics'!$D$1:$M$1,0)-1,COUNTIF(OFFSET('Sub Topics'!$D$1,1,MATCH($C2,'Sub Topics'!$D$1:$M$1,0)-1,99,1),"?*"),1)
OK

See attachment.

Thanks for the rep!