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!

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" wrote in message
...
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

Oh, that's not the answer i wanted hear.. :-) too bad.
well...
thank you for your help.
Do you know if there a way to group duplicate data in dropdown box?
so that 'Jan-05' will only shows up one time instead of 20 times?
Thanks

"Biff" wrote:

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" wrote in message
...
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

As far as I know, the data validation dropdown can contain 32767 items.
To create a unique list of items, you could use an Advanced Filter, to
extract the values to another worksheet. There are instructions in
Excel's Help, and he

http://www.contextures.com/xladvfilter01.html

GEORGIA wrote:
Oh, that's not the answer i wanted hear.. :-) too bad.
well...
thank you for your help.
Do you know if there a way to group duplicate data in dropdown box?
so that 'Jan-05' will only shows up one time instead of 20 times?
Thanks

"Biff" wrote:


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" wrote in message
...

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






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

32k number of choices is pretty big. Even if you got rid of 20 duplicates for
each unique value, you'd have more than 1000 entries.

Maybe using a set of cells to get to the correct one maybe easier.

Debra Dalgleish has some nice instructions for working with data|Validation and
dependent lists at:

http://www.contextures.com/xlDataVal02.html

(Scrolling through 1000 entries sounds kind of like a tough thing to make a user
do.)

GEORGIA wrote:

Oh, that's not the answer i wanted hear.. :-) too bad.
well...
thank you for your help.
Do you know if there a way to group duplicate data in dropdown box?
so that 'Jan-05' will only shows up one time instead of 20 times?
Thanks

"Biff" wrote:

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" wrote in message
...
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





--

Dave Peterson