| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Range (again)
All,
I am lost on this (Excel 2003) one. I have two different dynamic ranges I use for validation. The first is a text list of various items. This seems to work fine, but the second, is simply a range of numeric quantities (eg: 1-100) with the ability to add more. While first list does NOT display blanks at the bottom of the dropdown, the numeric one ALWAYS seems to do so and starts at the bottom. I've read the contextures article - but do not see what I'm doing wrong several suggestions said to delete blank cells in the range, but I have done this to the bottom of the sheet. Here are the formulas I use for the dynamic ranges. I should add that both ranges have a header in row 1. The text based list: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) and the Numeric one: =OFFSET(MasterContentsList!$E$2,0,0,COUNTA(MasterC ontentsList!$E:$E),1) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dynamic Range (again)
Maybe you have invisible characters or formulas showing blanks in the number
column Since you want numbers you can change the COUNTA to COUNT which will ignore text like "" or spaces etc -- Regards, Peo Sjoblom "Don" wrote in message ... All, I am lost on this (Excel 2003) one. I have two different dynamic ranges I use for validation. The first is a text list of various items. This seems to work fine, but the second, is simply a range of numeric quantities (eg: 1-100) with the ability to add more. While first list does NOT display blanks at the bottom of the dropdown, the numeric one ALWAYS seems to do so and starts at the bottom. I've read the contextures article - but do not see what I'm doing wrong several suggestions said to delete blank cells in the range, but I have done this to the bottom of the sheet. Here are the formulas I use for the dynamic ranges. I should add that both ranges have a header in row 1. The text based list: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) and the Numeric one: =OFFSET(MasterContentsList!$E$2,0,0,COUNTA(MasterC ontentsList!$E:$E),1) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dynamic Range (again)
Don,
Change COUNTA to COUNT. "Don" wrote: All, I am lost on this (Excel 2003) one. I have two different dynamic ranges I use for validation. The first is a text list of various items. This seems to work fine, but the second, is simply a range of numeric quantities (eg: 1-100) with the ability to add more. While first list does NOT display blanks at the bottom of the dropdown, the numeric one ALWAYS seems to do so and starts at the bottom. I've read the contextures article - but do not see what I'm doing wrong several suggestions said to delete blank cells in the range, but I have done this to the bottom of the sheet. Here are the formulas I use for the dynamic ranges. I should add that both ranges have a header in row 1. The text based list: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) and the Numeric one: =OFFSET(MasterContentsList!$E$2,0,0,COUNTA(MasterC ontentsList!$E:$E),1) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dynamic Range (again) -many thanks to both!
I don't how that escaped me as not even being tried yet but changing to COUNT
worked great! The thing I could think made the difference previously is that created the number list using CNTRL-drag to copy them down? otherwise no idea, as I know I deleted to the end of the sheet. Thanks again gents! "Don" wrote: All, I am lost on this (Excel 2003) one. I have two different dynamic ranges I use for validation. The first is a text list of various items. This seems to work fine, but the second, is simply a range of numeric quantities (eg: 1-100) with the ability to add more. While first list does NOT display blanks at the bottom of the dropdown, the numeric one ALWAYS seems to do so and starts at the bottom. I've read the contextures article - but do not see what I'm doing wrong several suggestions said to delete blank cells in the range, but I have done this to the bottom of the sheet. Here are the formulas I use for the dynamic ranges. I should add that both ranges have a header in row 1. The text based list: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) and the Numeric one: =OFFSET(MasterContentsList!$E$2,0,0,COUNTA(MasterC ontentsList!$E:$E),1) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dynamic Range (again)
Thanks! This now seems so obvious!
"Gary Mc" wrote: Don, Change COUNTA to COUNT. "Don" wrote: All, I am lost on this (Excel 2003) one. I have two different dynamic ranges I use for validation. The first is a text list of various items. This seems to work fine, but the second, is simply a range of numeric quantities (eg: 1-100) with the ability to add more. While first list does NOT display blanks at the bottom of the dropdown, the numeric one ALWAYS seems to do so and starts at the bottom. I've read the contextures article - but do not see what I'm doing wrong several suggestions said to delete blank cells in the range, but I have done this to the bottom of the sheet. Here are the formulas I use for the dynamic ranges. I should add that both ranges have a header in row 1. The text based list: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) and the Numeric one: =OFFSET(MasterContentsList!$E$2,0,0,COUNTA(MasterC ontentsList!$E:$E),1) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dynamic Range (again)
It should only make a difference if your cells are not empty
if they have formulas or blanks -- Regards, Peo Sjoblom "Don" wrote in message ... Thanks! This now seems so obvious! "Gary Mc" wrote: Don, Change COUNTA to COUNT. "Don" wrote: All, I am lost on this (Excel 2003) one. I have two different dynamic ranges I use for validation. The first is a text list of various items. This seems to work fine, but the second, is simply a range of numeric quantities (eg: 1-100) with the ability to add more. While first list does NOT display blanks at the bottom of the dropdown, the numeric one ALWAYS seems to do so and starts at the bottom. I've read the contextures article - but do not see what I'm doing wrong several suggestions said to delete blank cells in the range, but I have done this to the bottom of the sheet. Here are the formulas I use for the dynamic ranges. I should add that both ranges have a header in row 1. The text based list: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) and the Numeric one: =OFFSET(MasterContentsList!$E$2,0,0,COUNTA(MasterC ontentsList!$E:$E),1) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dynamic Range (again)
I am lost again. Now my text list does the same thing! I have deleted to the
bottom of the sheet. Admittedly, though when I look at the range in the defined names list, it highlights the one blank at the bottom of the range? But I know there is nothing there! "Peo Sjoblom" wrote: It should only make a difference if your cells are not empty if they have formulas or blanks -- Regards, Peo Sjoblom "Don" wrote in message ... Thanks! This now seems so obvious! "Gary Mc" wrote: Don, Change COUNTA to COUNT. "Don" wrote: All, I am lost on this (Excel 2003) one. I have two different dynamic ranges I use for validation. The first is a text list of various items. This seems to work fine, but the second, is simply a range of numeric quantities (eg: 1-100) with the ability to add more. While first list does NOT display blanks at the bottom of the dropdown, the numeric one ALWAYS seems to do so and starts at the bottom. I've read the contextures article - but do not see what I'm doing wrong several suggestions said to delete blank cells in the range, but I have done this to the bottom of the sheet. Here are the formulas I use for the dynamic ranges. I should add that both ranges have a header in row 1. The text based list: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) and the Numeric one: =OFFSET(MasterContentsList!$E$2,0,0,COUNTA(MasterC ontentsList!$E:$E),1) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dynamic Range (again)
Are the values in your list derived from formulas?
-- Regards, Peo Sjoblom "Don" wrote in message ... I am lost again. Now my text list does the same thing! I have deleted to the bottom of the sheet. Admittedly, though when I look at the range in the defined names list, it highlights the one blank at the bottom of the range? But I know there is nothing there! "Peo Sjoblom" wrote: It should only make a difference if your cells are not empty if they have formulas or blanks -- Regards, Peo Sjoblom "Don" wrote in message ... Thanks! This now seems so obvious! "Gary Mc" wrote: Don, Change COUNTA to COUNT. "Don" wrote: All, I am lost on this (Excel 2003) one. I have two different dynamic ranges I use for validation. The first is a text list of various items. This seems to work fine, but the second, is simply a range of numeric quantities (eg: 1-100) with the ability to add more. While first list does NOT display blanks at the bottom of the dropdown, the numeric one ALWAYS seems to do so and starts at the bottom. I've read the contextures article - but do not see what I'm doing wrong several suggestions said to delete blank cells in the range, but I have done this to the bottom of the sheet. Here are the formulas I use for the dynamic ranges. I should add that both ranges have a header in row 1. The text based list: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) and the Numeric one: =OFFSET(MasterContentsList!$E$2,0,0,COUNTA(MasterC ontentsList!$E:$E),1) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Range | New Users to Excel | |||
| Dynamic Range | Charts and Charting in Excel | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| dynamic range | Excel Discussion (Misc queries) | |||
| Dynamic Range | Excel Discussion (Misc queries) | |||
Linear Mode
