Forum Discussion
Dynamic Drop Down Lists
Hi,
I'm trying to set up a multi row schedule whereby once a selection is made from a Drop down list in Column B it automatically identifies the relative options in a Drop Down list in Column C (all info for the Drop Down lists sourced from a table in a separate worksheet.
I've had a go at it in the attached example workbook but feel it is a little Heath Robinson and probably unstable? (it generates numerous Circular reference errors) - I've tried to use the address of the active cell to generate the information in Column C's Drop Down list but not sure if this is the best or correct way to resolve this challenge. Ideally there is an Excel Formula solution as I have no experience of VBA or Pivot tables.
Any advice would be much appreciated.
Cheers
You may prepare data for it as
with
=TRANSPOSE(FILTER(Table1[Item],Table1[Location]=$Q2))and use for data validation in column C
=XLOOKUP($B2,Data!$Q$2#,Data!$R$2:$R$6)#
6 Replies
Thank you for taking the time to respond to my query. I had previously started working on the solution SergeiBaklan provided (which worked) and thus haven't used your solution but do appreciate your input.
Thanks again
You may prepare data for it as
with
=TRANSPOSE(FILTER(Table1[Item],Table1[Location]=$Q2))and use for data validation in column C
=XLOOKUP($B2,Data!$Q$2#,Data!$R$2:$R$6)#Thank you. Your solution has worked a dream. Being honest, I'm not quite sure how the various functions in the formulas you provided work but they do!......it gives me something to work on in developing my knowledge.
Thanks again
Jon_R1968 , you are welcome.
I'm not sure where this pattern is explained in details, but if you have concrete questions please ask.
Thank you for your advice and updating my workbook. I'll review all at work tomorrow and advise if any further queries or if your kind response has resolved everything.
Thanks again
