Use Indirect Function on Form Control DropDown List

Does anyone know how to use the INDIRECT function for the source of a dropdown list on a Form Control?

Here is how I can make it work on a regular cell:
Indirect Function on Regular Cell.JPG

Here is how I tried to make it work on a form control (didn't work):
Indirect Function on Form Control.JPG

Thanks in advance!

An untested theory.

Create a named range

Name:= ControlRange
Refers to:= =EVALUATE(Sheet1!$B$21) (remember to include the sheetname)

Then in the form control set the input range to =ControlRange

That worked 90%. Thanks!!
It behaves oddly if you change the dependant cell for the indirect reference.
See attached example:
1. Pick "District 20" on the first form control and choose "Field 10" on the second form control. Reference works fine this first time.
2. Then change the first form control to "District 16". The reference list is still working but the 'reference cell' that the new list is populating doesn't work.

I think I will need to add the clearing of the second form control 'upon change' of the first one just to re-set it. Otherwise it seems to work fine when the slate is cleared. Thanks!

It doesn't recognise the change when there is only one option in the 'Field' dropdown, to reset the second one, right click the first and Assign Macro, use the code below.

Please Login or Register  to view this content.

BTW, you have a few typos

Works perfectly!
I ended up using [B5] = "" and it clears it which is even better.
Thanks a million!!!
p.s. spilling is not my strong suit.

@jason.b75

Thanks for this. Would never have figured it out on my own.

@Oksana

Thanks for the example files. Would never have figured the answer out on my own.

What I'm doing wrong? I did everything as explained but still can't get it to work? Any idea?


00.jpg

Ah sorry, I didn't noticed there exist such a rule, sorry once again..

and yes, that's very clever rule I have to admit

Teo