Offset, named range and data validation

Question

I have the following formula:

=VERSCHUIVING($AJ$9;0;0;$AJ$17;1) or Offset($AJ$9,0,0,$AJ$17,1)in english

With AJ9 being the start of a vertical list of options, and AJ17 being the length of that list.

I use this in a named range and use it for data validation using the Indirect fucntion. When I want to select an argument from the list of this named range nothing appears, and when entering the Indirect formula it says it evaluates to an error. I tried entering the formula as an array formula using cmd + shift + enterand it only displayed the value of Cell AJ9 instead of expanding downwards to an array.

Any suggestions to make this work (on excel for mac)

EDIT:

Just tried on a windows version, also no succes. When I open the name manager and enter the formula there it does show the dotted lines around the correct selection. However when I turn on data validation it still evaluates to an error.

EDIT2:

If I enter =CriticalPractice(the name of my named range) in a cell it highlights cells AJ9 and AJ17. When I press enter I get #VALUE!. If I enter it as an array formula I get the value of cell AJ9.

Answer 1

The combination of dynamically-defined Names and INDIRECT is not permitted within Data Validation. In such cases you need to employ EVALUATE, though you should note that, despite no explicit VBA being called, this will nevertheless require that the workbook be saved as macro-enabled.

For example, assuming cell AL7 contains the text, e.g. "Name1", which is to be interpreted as the Defined Name, i.e. Name1 (whose definition is the OFFSET construction you give), then the following additional definition would be made in Name Manager:

Name: Name2 Refers to: =EVALUATE($AL$7)

After which the Data Validation list can be obtained via using the List option and entering:

=Name2

in the Source box.

Regards