Dynamic formula Data validation

[AussieVic]

Hi,

When i try to use the below dynamic formula in Data Validation i get a source error message. I tried to use the below as a list so i do not get any blanks in the list dropdown.

=MATCH(REPT("z",255),Assumptions!$E:$E)-ROW(Assumptions!$E$19)+1

 

[Aladin Akyurek]

Hi,

When i try to use the below dynamic formula in Data Validation i get a source error message. I tried to use the below as a list so i do not get any blanks in the list dropdown.

=MATCH(REPT("z",255),Assumptions!$E:$E)-ROW(Assumptions!$E$19)+1

=OFFSET(Assumptions!$E$19,0,0,MATCH(REPT("z",255),Assumptions!$E:$E)-MIN(ROW(Assumptions!$E$19))+1)

Note that you can't apply INDIRECT to the data-validated cell which uses the preceding definition.

 

[T. Valko]

Hi,

When i try to use the below dynamic formula in Data Validation i get a source error message. I tried to use the below as a list so i do not get any blanks in the list dropdown.

=MATCH(REPT("z",255),Assumptions!$E:$E)-ROW(Assumptions!$E$19)+1

Maybe you need something like this:

=Assumptions!$E$19:INDEX(Assumptions!$E:$E,MATCH("zzzzz",Assumptions!$E:$E))

Note that if data validation list is on another sheet then you'll have to give that formula defined name and use the defined name as the source.