I would use your formula with apostrophes around the worksheet name:

=OFFSET('TST Week'!$A$1,COUNTA('TST Week'!$A:$A),0,COUNTA('TST Week'!$A:$A)-1,1)

But since you have A1 empty (not even a formula that evaluates to ""), and I
don't want to include A2, then I'd modify your formula:

=OFFSET('Tst Week'!$A$2,
COUNTA('TST Week'!$A:$A),0,COUNTA('Tst Week'!$A:$A)-1,1)

But it seems more natural to me to start at A3 and go down the count of entries
minus 1.

=OFFSET('Tst Week'!$A$3,0,0,COUNTA('Tst Week'!$A:$A)-1,1)

Debra Dalgleish explains dynamic range names he
http://contextures.com/xlNames01.html#Dynamic


kal4000 wrote:

I am trying to define a dynamic name for a drop down menu. I have a
worksheet named TST Week with a list of dates starting in cell A3 (Date
header in A2, nothing in A1). I would like to be able to add indefinite more
dates in the future in column A and have those show up on the drop down menu
as they are added as well as all the previous dates. However, when I try
this equation, it evaluates to an error. =OFFSET(TST Week!$A$1,COUNTA(TST
Week!$A:$A),0,COUNTA(TST Week!$A:$A)-1,1). Where am I going wrong?

--

Dave Peterson