Formula works in spreadsheet but not in Name Manager

[sparky2205]

Hi folks,
this formula ...

="Sheet1!$B$2:" & "Sheet1!" & ADDRESS(2,COLUMNS(DataTable),1)

... works in a spreadsheet but doesn't work when I use it in Name Manager as the range for a Named Range.

If more detail is required I can provide it but I'm assuming this is either not possible or I have made a mistake in my syntax.

I'm trying to create a dynamic Named Range based on rows that will change as columns are added to a table.

 

[sparky2205]

@RoryA works perfectly.
Thanks a million.

 

[sparky2205]

It would be perfect if it were dynamic i.e. when I add a row or column to the table the data validation rule extends to the new fields.
But I presume that would require some VBA.

 

[RoryA]

Yes, or swap the orientation of the table.