A
1 Week
2 May 26 - May 31, 2008
3 June 2 - June 7, 2008
4 June 9 - June 14, 2008
5 June 16 - June 21, 2008

I have what is above in my TST Week sheet.
I have defined Week_Range to be =OFFSET('TST Week'!$A$1,2,0,COUNT('TST
Week'!$A:$A)-1,1) and my Data Validation to be
=OFFSET(Week_Range,0,0,COUNTA(Week_Range),1). However, I still only get the
first entry May 26 - May 31, 2008 in my drop down menu. It's like it's not
reading the COUNT function to tell it the height... The only way I can get
all 4 dates is if I put Week_Range to be =OFFSET('TST Week'!$A$1,2,0,4,1)...
but I can't do that because the length of the dates list will be growing.
What am I doing wrong?

I changed the equation you gave me

"Thomas [PBD]" wrote:

I see. What we will have to do then is to set a named range for the "Week"
column. Choose the column (A) for your Weeks and Define it. I used
WEEK_RANGE as my name. Then set your validation setting to include Don
Guillett's formula, setting the range instead of the columns. PS - I am
assuming that you are using this drop-down in a worksheet other than that of
the Weeks Listing... either way this will work.

Validation Source:
=OFFSET(WEEK_RANGE,2,0,COUNTA(WEEK_RANGE),1)

This will return only the values of the Week numbers.

--
--Thomas [PBD]
Working hard to make working easy.


"kal4000" wrote:

What if I want to return the whole list and not just the last value of the
Dates? I want all the Dates options to be in the drop down menu. Does that
make sense?

I want my equation to do the following if possible: =OFFSET(TST
Week!$A$1,COUNTA(TST Week!$A:$A),0,COUNTA(TST Week!$A:$A)-1,1) starts at cell
A1 in sheet TST Week, moves down the number of occupied cells in column A (to
the bottom of the list which is changing in length each week), moves over 0
columns, then encompasses the entire A column of dates minus the Dates header.

"Thomas [PBD]" wrote:

Kal,

The coding for your offset is the issue.
The coding should state (Starting Point, Number of Rows Up/Down, Number of
Columns Left/Right. This coding below will return the last value of the
Dates.

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

--
--Thomas [PBD]
Working hard to make working easy.


"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?