| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Define Dynamic Name
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Define Dynamic Name
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 "kal4000" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Define Dynamic Name
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Define Dynamic Name
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 "kal4000" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Define Dynamic Name
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Define Dynamic Name
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 "kal4000" wrote in message ... 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 "kal4000" wrote in message ... 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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Define Dynamic Name
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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Define Dynamic Name
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Define Dynamic Name
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? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Define Dynamic Name
When I do what you gave me (below), it only gives me the first entry in my
Dates list... =OFFSET('TST Week'!$A$3,0,0,COUNT('TST Week'!$A:$A)-1,1) "Dave Peterson" wrote: 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Define Dynamic Name
You changed my formula.
I used =counta(), not =count(). =counta() counts everything. =count() only counts numbers. kal4000 wrote: When I do what you gave me (below), it only gives me the first entry in my Dates list... =OFFSET('TST Week'!$A$3,0,0,COUNT('TST Week'!$A:$A)-1,1) "Dave Peterson" wrote: 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 -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Define Dynamic Name
You are right, I did forget the A. Thanks so much! It works perfectly now.
"Dave Peterson" wrote: You changed my formula. I used =counta(), not =count(). =counta() counts everything. =count() only counts numbers. kal4000 wrote: When I do what you gave me (below), it only gives me the first entry in my Dates list... =OFFSET('TST Week'!$A$3,0,0,COUNT('TST Week'!$A:$A)-1,1) "Dave Peterson" wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| dynamic search in the Define Name window | Excel Discussion (Misc queries) | |||
| Dynamic Formula with Dynamic Address | Excel Worksheet Functions | |||
| Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Linear Mode
