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