Dynamic formula Data validation

AussieVic

Active Member
Joined
Jan 9, 2009
Messages
364
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,791
Messages
5,524,897
Members
409,610
Latest member
db321

This Week's Hot Topics

Top