dynamic range

[GEORGIA]

I just learned this new function so please bare with me.
I have created name range
=OFFSET(qryATL!$A$2,0,0,COUNT(qryATL!$A:$A),1) ( this is column with a date)
and named it "qryATL".
I tried to use the validation list so that there will be a dropdown to
choose the date.
However, i don't know if because there are 64,000 rows, there are nothing
there on my validation list.
I went Validation, from the list, source: =qryATL.

so I deleted some rows, and left 15 rows, and I see the list on the dropdown
box. Is there a limit to the drop down box?
Also, is there anyway to group the dropdown box? for example, I have
several 1-23-05, i want to have the drop down box show that once.

Thank you!

[Dave Peterson]

Maybe it's not the dynamic range formula that's causing the trouble.

If you put:
=COUNT(qryATL!$A:$A)
in b1 of qryATL, what do you get back.

=Count() counts numbers.
and maybe you don't have real dates in column A.

(=counta() counts alpha or numeric entries)


GEORGIA wrote:
>
> I just learned this new function so please bare with me.
> I have created name range
> =OFFSET(qryATL!$A$2,0,0,COUNT(qryATL!$A:$A),1) ( this is column with a date)
> and named it "qryATL".
> I tried to use the validation list so that there will be a dropdown to
> choose the date.
> However, i don't know if because there are 64,000 rows, there are nothing
> there on my validation list.
> I went Validation, from the list, source: =qryATL.
>
> so I deleted some rows, and left 15 rows, and I see the list on the dropdown
> box. Is there a limit to the drop down box?
> Also, is there anyway to group the dropdown box? for example, I have
> several 1-23-05, i want to have the drop down box show that once.
>
> Thank you!

--

Dave Peterson

[GEORGIA]

Yes, you are absolutely right. The date is formated as text because I had to
extract a date 1-26-2005 as "Jan-05". formatting as MMM-YY caused the problem
because doing pivot table, it still shows as MMM-DD-YY. anyhow, that was
another issue.
I did counta instead of count
=OFFSET(qryATL!$A$2,0,0,COUNTA(qryATL!$A:$A),1)
and did the validation, and it is still not showing anything.
when I did =counta(qryATL!$A:$A) i get 64465, which is the # of records.

what else am i doing wrong?


"Dave Peterson" wrote:

> Maybe it's not the dynamic range formula that's causing the trouble.
>
> If you put:
> =COUNT(qryATL!$A:$A)
> in b1 of qryATL, what do you get back.
>
> =Count() counts numbers.
> and maybe you don't have real dates in column A.
>
> (=counta() counts alpha or numeric entries)
>
>
> GEORGIA wrote:
> >
> > I just learned this new function so please bare with me.
> > I have created name range
> > =OFFSET(qryATL!$A$2,0,0,COUNT(qryATL!$A:$A),1) ( this is column with a date)
> > and named it "qryATL".
> > I tried to use the validation list so that there will be a dropdown to
> > choose the date.
> > However, i don't know if because there are 64,000 rows, there are nothing
> > there on my validation list.
> > I went Validation, from the list, source: =qryATL.
> >
> > so I deleted some rows, and left 15 rows, and I see the list on the dropdown
> > box. Is there a limit to the drop down box?
> > Also, is there anyway to group the dropdown box? for example, I have
> > several 1-23-05, i want to have the drop down box show that once.
> >
> > Thank you!
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Maybe you have spaces in those "blank" cells????

Do you still have formulas in those cells? If yes, they get counted with
=counta(), too.

Or if you had formulas that evaluated to "" and you converted to them to values
(edit|copy, edit|paste special|values), you could have some junk left over.

If that's the case, you can do this:
select your column(s)
edit|replace
what: (leave blank)
with: $$$$$ (some unique string)
replace all

Then
edit|replace
what: $$$$$
with: (leave blank)
replace all

This cleans up those cells.


GEORGIA wrote:
>
> Yes, you are absolutely right. The date is formated as text because I had to
> extract a date 1-26-2005 as "Jan-05". formatting as MMM-YY caused the problem
> because doing pivot table, it still shows as MMM-DD-YY. anyhow, that was
> another issue.
> I did counta instead of count
> =OFFSET(qryATL!$A$2,0,0,COUNTA(qryATL!$A:$A),1)
> and did the validation, and it is still not showing anything.
> when I did =counta(qryATL!$A:$A) i get 64465, which is the # of records.
>
> what else am i doing wrong?
>
> "Dave Peterson" wrote:
>
> > Maybe it's not the dynamic range formula that's causing the trouble.
> >
> > If you put:
> > =COUNT(qryATL!$A:$A)
> > in b1 of qryATL, what do you get back.
> >
> > =Count() counts numbers.
> > and maybe you don't have real dates in column A.
> >
> > (=counta() counts alpha or numeric entries)
> >
> >
> > GEORGIA wrote:
> > >
> > > I just learned this new function so please bare with me.
> > > I have created name range
> > > =OFFSET(qryATL!$A$2,0,0,COUNT(qryATL!$A:$A),1) ( this is column with a date)
> > > and named it "qryATL".
> > > I tried to use the validation list so that there will be a dropdown to
> > > choose the date.
> > > However, i don't know if because there are 64,000 rows, there are nothing
> > > there on my validation list.
> > > I went Validation, from the list, source: =qryATL.
> > >
> > > so I deleted some rows, and left 15 rows, and I see the list on the dropdown
> > > box. Is there a limit to the drop down box?
> > > Also, is there anyway to group the dropdown box? for example, I have
> > > several 1-23-05, i want to have the drop down box show that once.
> > >
> > > Thank you!
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[GEORGIA]

No forumals and no blank cells. But just incase I did tried to find and
replace, but since there are no blank spaces, it gave me a message stating
excel could not find it to repace. Is it possible that dropdown has value
limitation?

As I stated earlier, If i delete some rows, leaving only 15 rows...it works.

Thank You!

"Dave Peterson" wrote:

> Maybe you have spaces in those "blank" cells????
>
> Do you still have formulas in those cells? If yes, they get counted with
> =counta(), too.
>
> Or if you had formulas that evaluated to "" and you converted to them to values
> (edit|copy, edit|paste special|values), you could have some junk left over.
>
> If that's the case, you can do this:
> select your column(s)
> edit|replace
> what: (leave blank)
> with: $$$$$ (some unique string)
> replace all
>
> Then
> edit|replace
> what: $$$$$
> with: (leave blank)
> replace all
>
> This cleans up those cells.
>
>
> GEORGIA wrote:
> >
> > Yes, you are absolutely right. The date is formated as text because I had to
> > extract a date 1-26-2005 as "Jan-05". formatting as MMM-YY caused the problem
> > because doing pivot table, it still shows as MMM-DD-YY. anyhow, that was
> > another issue.
> > I did counta instead of count
> > =OFFSET(qryATL!$A$2,0,0,COUNTA(qryATL!$A:$A),1)
> > and did the validation, and it is still not showing anything.
> > when I did =counta(qryATL!$A:$A) i get 64465, which is the # of records.
> >
> > what else am i doing wrong?
> >
> > "Dave Peterson" wrote:
> >
> > > Maybe it's not the dynamic range formula that's causing the trouble.
> > >
> > > If you put:
> > > =COUNT(qryATL!$A:$A)
> > > in b1 of qryATL, what do you get back.
> > >
> > > =Count() counts numbers.
> > > and maybe you don't have real dates in column A.
> > >
> > > (=counta() counts alpha or numeric entries)
> > >
> > >
> > > GEORGIA wrote:
> > > >
> > > > I just learned this new function so please bare with me.
> > > > I have created name range
> > > > =OFFSET(qryATL!$A$2,0,0,COUNT(qryATL!$A:$A),1) ( this is column with a date)
> > > > and named it "qryATL".
> > > > I tried to use the validation list so that there will be a dropdown to
> > > > choose the date.
> > > > However, i don't know if because there are 64,000 rows, there are nothing
> > > > there on my validation list.
> > > > I went Validation, from the list, source: =qryATL.
> > > >
> > > > so I deleted some rows, and left 15 rows, and I see the list on the dropdown
> > > > box. Is there a limit to the drop down box?
> > > > Also, is there anyway to group the dropdown box? for example, I have
> > > > several 1-23-05, i want to have the drop down box show that once.
> > > >
> > > > Thank you!
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

How many rows of data did you expect?

You said that this:
=counta(qryATL!$A:$A)
returned 64465

So there's something in there.

Did you get the data from a web site? Maybe it's some of those HTML
non-breaking spaces (char(160)).

If you put
=len(a2)
and copy down all the column, do you see 0's or do you see numbers bigger than
0?



GEORGIA wrote:
>
> No forumals and no blank cells. But just incase I did tried to find and
> replace, but since there are no blank spaces, it gave me a message stating
> excel could not find it to repace. Is it possible that dropdown has value
> limitation?
>
> As I stated earlier, If i delete some rows, leaving only 15 rows...it works.
>
> Thank You!
>
> "Dave Peterson" wrote:
>
> > Maybe you have spaces in those "blank" cells????
> >
> > Do you still have formulas in those cells? If yes, they get counted with
> > =counta(), too.
> >
> > Or if you had formulas that evaluated to "" and you converted to them to values
> > (edit|copy, edit|paste special|values), you could have some junk left over.
> >
> > If that's the case, you can do this:
> > select your column(s)
> > edit|replace
> > what: (leave blank)
> > with: $$$$$ (some unique string)
> > replace all
> >
> > Then
> > edit|replace
> > what: $$$$$
> > with: (leave blank)
> > replace all
> >
> > This cleans up those cells.
> >
> >
> > GEORGIA wrote:
> > >
> > > Yes, you are absolutely right. The date is formated as text because I had to
> > > extract a date 1-26-2005 as "Jan-05". formatting as MMM-YY caused the problem
> > > because doing pivot table, it still shows as MMM-DD-YY. anyhow, that was
> > > another issue.
> > > I did counta instead of count
> > > =OFFSET(qryATL!$A$2,0,0,COUNTA(qryATL!$A:$A),1)
> > > and did the validation, and it is still not showing anything.
> > > when I did =counta(qryATL!$A:$A) i get 64465, which is the # of records.
> > >
> > > what else am i doing wrong?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Maybe it's not the dynamic range formula that's causing the trouble.
> > > >
> > > > If you put:
> > > > =COUNT(qryATL!$A:$A)
> > > > in b1 of qryATL, what do you get back.
> > > >
> > > > =Count() counts numbers.
> > > > and maybe you don't have real dates in column A.
> > > >
> > > > (=counta() counts alpha or numeric entries)
> > > >
> > > >
> > > > GEORGIA wrote:
> > > > >
> > > > > I just learned this new function so please bare with me.
> > > > > I have created name range
> > > > > =OFFSET(qryATL!$A$2,0,0,COUNT(qryATL!$A:$A),1) ( this is column with a date)
> > > > > and named it "qryATL".
> > > > > I tried to use the validation list so that there will be a dropdown to
> > > > > choose the date.
> > > > > However, i don't know if because there are 64,000 rows, there are nothing
> > > > > there on my validation list.
> > > > > I went Validation, from the list, source: =qryATL.
> > > > >
> > > > > so I deleted some rows, and left 15 rows, and I see the list on the dropdown
> > > > > box. Is there a limit to the drop down box?
> > > > > Also, is there anyway to group the dropdown box? for example, I have
> > > > > several 1-23-05, i want to have the drop down box show that once.
> > > > >
> > > > > Thank you!
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson