Excel Filter Question/Problem

[Bg]

Have a small spread sheet with about 200 rows of books that I received from
a data base. I added a column to reflect the year published. so that I could
filter and sort. I enable the auto-filter to the headers and try to perform
a custom filter to select a rows with criteria "greater than - 1969" and"
less than 2002". Although there are 50 -60 titles in this range, the filter
return zero. I have used filters in many other application, but can't seem
to figure out why I can't get any results. Is there something in the cell
formatting...? What am I missing?

TIA
Reg

 

[Tyro]

Are the years stored as text or dates?

 

[Guest]

I think it is because the data in your column is text as apposed to to number

 

[Gord Dibben]

If you entered the years as real dates, Excel doesn't know what 1969 is.

If entered as text or general then Excel filter will find them.

If real dates, select the dates from the drop-downs to the right of the "is
greater than" and "is less than" dialogs.


Gord Dibben MS Excel MVP

 

[Bg]

Yes.....I did try all the different cell formats and used the drop-down to
make the selections to no avail It will filter by individual "year" and
return all those years, but not any range at all.
Puzzled.
Thanks to all that replied.
Reg

 

[Gord Dibben]

How did you enter the dates into the column you added and what do these look
like?

If I enter 1/1/1954 in A1 and 1/1/1955 in A2 then fill down to A52 I can filter
by "greater than" 1/1/969(from the dropdown) and "less than" 1/1/2002(from
the dropdown).

Is this similar to what you have done?


Gord

 

[Bg]

The column I added is just the year "1969", etc. I changed them all to
1/1/1969, etc and tried it. Zero return, again. After checking in the help
area I found some info that might suggest I received a List which has
different filtering methods. When I get my "zero" returns there is a blue
asterisk in the far left column in row 748. There is also a blue box around
the entire set of data. Does this have anything to do with my issue?? Can I
remove a "List? I tried to copy and paste the data into a different
workbook....same thing. Arg-g-g-g.
Reg

 

[Gord Dibben]

I cannot replicate your problem whether or not I enter the years as a number
like 1964, 1965 etc. or as a date like 1/1/1964

Are you sure you are filtering on that column with the years?

The range being a "list" should not make a difference.

I tried filtering after creating a list from Data>List>Create List anf got good
results.

To delete a list area just right-click anywhere in the list and List>Convert to
Range.


Gord