Using cell to filter data in drop down

[imdumb]

hello there,
finding this forum super helpful, really appreciate everyone's assistance. I have another problem which I would really appreciate some help on

document attached for further clarity.

in [Sheet1] i'm looking to use the values in column A to look at the values in [data] and then provide the corresponding values in a drop down... for example, if Cooktop Type appears in [Sheet1] cell A1, then in [Sheet] cell B1 I'd like a drop down to show "induction" or "other", another example would be if in [Sheet1] cell A5 showed 'Diopter' then in B5 I'd like a drop down showing the options "'1" and "'1.5" as shown in the [data] sheet.

I hope this all makes sense, I'd really appreciate any help that can be provided!

thanks

[JohnTopley]

Create a set of named ranges as per attached:

in F2

=IFERROR(INDEX(data!$B$1:$B$100,AGGREGATE(15,6,ROW($A$1:$A$100)/(data!$A$1:$A$100="colour"),ROWS($1:1))),"")

in B1

Data Validation ..List ..=INDIRECT(SUBSTITUTE($A1," ","_")&"_DD")

[AliGW]

Further to John's solution, as you have 365, you can use this in F2:

=FILTER(data!$B$1:$B$29,data!$A$1:$A$29="Colour")

[AliGW]

Out of interest, why are you not using the VBA solution you received here: https://www.excelforum.com/excel-pro...5-columns.html