What's wrong with using this named range for List validation?

Question

I have a column of data that will have more values added to it over time. I would like to use this column's values for List validation on another worksheet, such that the drop-down options reflect the current contents of the column. I tried creating a named range ListColumn with the following formula:

=ADDRESS(3,12,1,1,"DataSource")&":"&ADDRESS(COUNTA('DataSource'!$L:$L),12,1,1,"DataSource") 

When I try to add validation for a cell, choosing Allow: 'List' and entering =ListColumn in the formula box, I get this error message:

The list source must be a delimited list, or a reference to a single row or column.

I tried wrapping the whole formula in an INDIRECT(), and while that didn't generate an error on the Name Manager screen, when I tried to add the validation, I got this error message:

The Source currently evaluates to an error.  Do you want to continue?

I'm not sure what's wrong here. The second parameter of both ADDRESS() calls is the same, so the range address should refer to a single column. How can I set up the dynamic list validation that I'm looking for?

EDIT: Debugging this is producing some odd behavior. I keep getting a run-time error when I try to select ListColumn using Range("ListColumn").Select. Message is "Method 'Range' of object '_Global' failed."

Entering =ListColumn returns: 'DataSource'!$L$3:'DataSource'!$L$65, which is the expected value.

Also, when I enter the formula =COUNTA(ListColumn) it returns "1", but when I enter the formula =COUNTA('DataSource'!$L$3:'DataSource'!$L$65) it returns the correct value of "63". =COUNTA(INDIRECT(ListColumn)) also returns "1".

Answer 1

I believe the issue may be the inclusion DataSource as the text sheet name in the second ADDRESS block. I get the exact same behavior as you do when performing your steps above, but it seems to work with this:

=INDIRECT(ADDRESS(3,12,1,1,"DataSource")&":"&ADDRESS(COUNTA(DataSource!$L:$L),12,1,1))

You can then do exactly what you did above and use it in a drop-down. My best assumption would be that since you are already defining the sheet in the first portion of the address, you do not need to provide it in the second portion (and it will cause an error if you do). If you think about it from a program perspective, it makes sense (I think :) ), since it would be difficult/impossible to refer to ranges over multiple sheets, and therefore the syntax likely doesn't allow for a second sheet to be specified after the colon (even if it is the same). That is just conjecture though (probably wrong), so hopefully the solution works :)