dynamic range

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

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
>

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

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
>

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

yes, there are 64465 rows.
if i put =len(a2) in column "A" (where my name range is based from) then
returns 0, if I put in column b, it returns 6.
the data is exported out from Oracle.
Can I send you the actual file so you can see what i'm looking at?

thank you


"Dave Peterson" wrote:

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

Hi!

There seems to be a limit as to how many items can populate a data
validation drop down list.

I put a value in every cell in column A, A1:A65536

I then created a DD and used A:A as the source. The DD was empty when the
the entire column was used as the source. I then started deleting values in
column A in increments of 10,000 until something showed up in the DD.
Something finally showed up when the range size was 25,000 entries.
So........a drop down will hold at least 25,000 entries.

Biff

"GEORGIA" <GEORGIA@discussions.microsoft.com> wrote in message
news:1BC630D8-93DB-43EE-A556-2A010A3ABE7E@microsoft.com...
> yes, there are 64465 rows.
> if i put =len(a2) in column "A" (where my name range is based from) then
> returns 0, if I put in column b, it returns 6.
> the data is exported out from Oracle.
> Can I send you the actual file so you can see what i'm looking at?
>
> thank you
>
>
> "Dave Peterson" wrote:
>
>> 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
>>