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?

Try it this way when on the sheet desired. Excel will fill in the sheet name
=OFFSET($A$1,0,0,COUNTA($A:$A)-1,1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"kal4000" <(E-Mail Removed)> wrote in message
news:74C6BE34-D2D1-4705-9A40-(E-Mail Removed)...
>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?

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?

Even if my drop down is on a different sheet than the dynamic list?

"Don Guillett" wrote:

> Try it this way when on the sheet desired. Excel will fill in the sheet name
> =OFFSET($A$1,0,0,COUNTA($A:$A)-1,1)
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "kal4000" <(E-Mail Removed)> wrote in message
> news:74C6BE34-D2D1-4705-9A40-(E-Mail Removed)...
> >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?
>
>

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?

The list and defined name should be the same sheet. In your data validation
list =mylist
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"kal4000" <(E-Mail Removed)> wrote in message
news:20F053B2-B573-4B7D-9AC1-(E-Mail Removed)...
> Even if my drop down is on a different sheet than the dynamic list?
>
> "Don Guillett" wrote:
>
>> Try it this way when on the sheet desired. Excel will fill in the sheet
>> name
>> =OFFSET($A$1,0,0,COUNTA($A:$A)-1,1)
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "kal4000" <(E-Mail Removed)> wrote in message
>> news:74C6BE34-D2D1-4705-9A40-(E-Mail Removed)...
>> >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?
>>
>>

I did what you said - letting Excel fill in the sheet and when I click OK, it
gives me "The Source currently evaluates to an error. Do you want to
continue?"

"Don Guillett" wrote:

>
> The list and defined name should be the same sheet. In your data validation
> list =mylist
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "kal4000" <(E-Mail Removed)> wrote in message
> news:20F053B2-B573-4B7D-9AC1-(E-Mail Removed)...
> > Even if my drop down is on a different sheet than the dynamic list?
> >
> > "Don Guillett" wrote:
> >
> >> Try it this way when on the sheet desired. Excel will fill in the sheet
> >> name
> >> =OFFSET($A$1,0,0,COUNTA($A:$A)-1,1)
> >>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "kal4000" <(E-Mail Removed)> wrote in message
> >> news:74C6BE34-D2D1-4705-9A40-(E-Mail Removed)...
> >> >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?
> >>
> >>
>
>

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?

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 here:
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

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?