I have stolen (collaborated) some code (which to be honest I am still digesting so as to learn how it works), but in the mean time how can I enter this array formula into Name Manager and have it work as an Array Formula. I have created a name called =EmailList this email list is supposed to generate a list of email addr's. I have then used Data Validation to create the dropdown list to ensure that only the entered Email addr's on the User Information sheet is clickable. I created an offset formula that will allow me to generate the dynamic list in the drop down but its not alphabetized its as its all entered. So I have tweaked the Array Formula but when I click the drop down it doesn't work it seems to be acting as a formula instead of an Array Formula. So my question is how can I enter the Array Formula into Name Manager and have it work as an Array Formula?


Works but is not alphabetized:

=OFFSET('User Information'!$A$1, 0, 0, COUNTA('User Information'!$A$1:$A$1001))



Does not work at all:

=IFERROR(INDEX('User Information'!$A$2:$A$10001,MATCH(SMALL(NOT('User Information'!$A$2:$A$10001="")*IF(ISNUMBER('User Information'!$A$2:$A$10001),COUNTIF('User Information'!$A$2:$A$10001,"<="&'User Information'!$A$2:$A$10001),COUNTIF('User Information'!$A$2:$A$10001,"<="&'User Information'!$A$2:$A$10001)+SUM(--ISNUMBER('User Information'!$A$2:$A$10001))),ROWS('User Information'!$A$2:A2)+SUM(--ISBLANK('User Information'!$A$2:$A$10001))),NOT('User Information'!$A$2:$A$10001="")*IF(ISNUMBER('User Information'!$A$2:$A$10001),COUNTIF('User Information'!$A$2:$A$10001,"<="&'User Information'!$A$2:$A$10001),COUNTIF('User Information'!$A$2:$A$10001,"<="&'User Information'!$A$2:$A$10001)+SUM(--ISNUMBER('User Information'!$A$2:$A$10001))),0)),"")


Another question instead of doing $A$2:$A$10001 how can i have it do all rows in column 1 of Table1 which is really what 'User Information'!$A$2:$A$10001 really is since there won't be 10001 rows I just put threw a number out there there has to be a way to have the formula figure out end of row is or something like that.


Thanks in advance but this is the first time I've done something like this so I am really still learning lol.