Advanced filter stopped working

[FLJohnson]

I have an spreadsheet that I filter monthly using the advanced filter. It works perfectly every time until I added a new column header ("2014") to the next column to the right of the existing columns in my source spreadsheet and added the same column header (copying and pasting the header) to my criteria spreadsheet to the right of the last column in the criterion spreadsheet. I entered the new criteria in the 2014 criterion column (which actually should not match anything in the source spreadsheet because "2014" has just started and there are no entries in the 2014 source column. I also adjusted the date value for another criterion column which should limit the hits further than the previous setting. Now when I apply the advanced filter, very few records are being filtered. (I usually get 92 hits from 1267 records.) Now I'm getting 1205 hits from 1267 records. If I then delete both of the new columns added to the source and criteria spreadsheets--now the spreadsheets are identical to the spreadsheets that were working before--and use the advanced filter, I continue to get 1205 hits again. If I change the date criterion back to its previous values, I continue to get 1205 hits. I should be getting the 92 that I got before. If I revert to the original spreadsheet, the advanced filter works if the only thing I change is the date criterion. (I get 84 hits with the more stringent date criterion as I should.)

It seems that the spreadsheet becomes corrupted by simply added the column header in each spreadsheet.

 

[jim may]

A cople things come to mind... 1) Does filter expected 2014 as Numeric or TEXT? 2) Check the Advanced-Filter Dialog box for proper Range Settings.

 

[FLJohnson]

A cople things come to mind... 1) Does filter expected 2014 as Numeric or TEXT? 2) Check the Advanced-Filter Dialog box for proper Range Settings.

Thanks for your response.

Correction: The actual column headers I used were "Giver 2014" (not "2014"), so the column header is text. Also, the entries in the source column and the criterion column are simply an "x" (or blank), so that I can identify who gave in 2014.

I have checked the range settings at least 30 times in my attempts to make this work, but I don't really understand how the range being miss-set could give me the results I'm getting.

In addition, the new column is column AD. Have I reached a limit on number of columns?

 

[FLJohnson]

Update:

I just tested the hypothesis that the number of columns was exceeding some limit that the advanced filter could work with. I deleted five unnecessary columns from the source spreadsheet and the corresponding columns from the criterion spreadsheet.

The advanced filter is now working!

Is this a known issue?

 

[FLJohnson]

I just determined that if I hide instead of deleting the columns I mentioned in my last post, the advanced filter works just as it did when I deleted the columns.

This seems bizarre.

 

[FLJohnson]

Here is a revision of my last post:

I just determined that if I hide instead of deleting the columns I mentioned in my last post, the advanced filter works just as it did when I deleted the columns.

Pressing on: Just to be certain, I unhid the columns, deleted the columns, and reran the filter just to make sure I was getting the same results. I did. Then I undeleted the columns, and now the filter is working as if nothing were wrong in the first place. I saved this workbook that is now working properly as a new name.

Then I re-opended the workbook that before could not be filtered properly when I made added the new columns. Now when I add the new columns to the source and the criteria worksheets, the advanced filter works fine. It is behaving as if something was originally malfunctioning and then just disappeared. I swear I had the ranges correct and I have no idea what was wrong before. I must have attempted to make the filter work at least 30 times by renaming the columns, reformatting the columns, and a dozen other things. Nothing worked. Now I can't reproduce the bad behavior.

 

[FLJohnson]

The original problem just returned. I'm now getting 1025 hits on the same workbook that was working hours earlier. There must be something very peculiar causing this malfunction. I'll try to report anything I learn.

I have not been able to determine what causes the problem. Once I start getting 1025 hits on the filtering, I can't seem to do anything to get it to work. It behaves as if something sets the workbook to malfunction and nothing can fix it. I'll try to close it and go back to the old tricks to make it work.

 

[FLJohnson]

This is very humbling and embarrassing, but I discovered what I was doing wrong with my filtering. I had deleted an entry in one of my criteria rows for one criterion column and I had inadvertently created a criterion that would hit on almost all of the records. I apologize to anyone who has been following this thread and to Jim for wasting his time. Please forgive my hasty posts. I think the reason I made this mistake is that I only have to revise the criteria in this workbook once a year, and I simply forgot this one criterion that needs to be changed annually.