Data validation list using a function

[insomniac53]

I am trying to create a dropdown validation list on a sheet cell (not a form) using a function rather than a constant. I am doing this because the validation will be repeated on the same sheet for various columns, so rather than create 20+ Names, I thought I might be able to write a function which returns a string, identical to a constant string. So, the constant Name for column I is 'ValidCategories' and refers to this range:

='Lists Sheet'!$I$2:INDEX('Lists Sheet'!$I:$I,MATCH(REPT("z",255),'Lists Sheet'!$I:$I))

The function (abridged) is:

Public Function Valid_Range(id As Integer) As String
    'id selects the appropriate column 
    Dim s as String
    's creates same string as above for the selected column
    Valid_Range = s
End Function

The string function works okay, but the validation needs a specified list or Name. Is there a way around this or do I need a different approach?
Thanks

 

[FDibbins]

If you want the same DD across a bunch of cells, why not just create the 1st 1, and copy across?

 

[insomniac53]

Thanks, but the dropdowns (DDs?) are all different. The parameter in the function determines the dropdown contents. But like I say, the validation source won't accept the function, just a defined Name or list.