Excel AutoFilter Problem

[Shereene]

Can someone please help me with this problem I have with Excel autofilter.

I applied an autofilter to a list of data that does not exceed the 1000
items limit, and attempted to filter the data to get a particular item. The
filter did bring up all the items needed, but it also had at the bottom of
the filtered list, another item that was not part of the items to be
filtered, which unfortunately affected my subtotal calculation. Can some one
please advise.

Thanks so much

 

[bj]

Was the odd item in the range that was filtered?
if not, you may need to change your method for range selection (I have been
burnt by this)

 

[Shereene]

Hi BJ:

Yes the odd item ended up in the filtered list and as a result it affects
the calculation of the subtotal for the item I wanted filtered . I tried
reselecting the data range by reselecting the column headings as well as
tried selecting only the body of data, but still does not help. Any ideas?

 

[Shereene]

Hi:

I also realise that when I apply the autofilter to the data, I do not get
the (blanks), (nonblanks) option from the drop-down list. Also if I add a new
entry that has not been in the list before, it does not show up in the
drop-down.
Shereene

 

[Alan Beban]

I can't reproduce your problem. Perhaps you could indicate it by
describing more fully what happens: i.e., the range to be filltered,
where the blanks are, where the unwanted data is, what you select, where
you add a new entry. You don't need to use the whole range if it's too
large; just enough to illustrate the problem.

Alan Beban

 

[Shereene]

Hi Alan:

This is an example of part of the sheet with items based on date of
requistion:

Item Code Item Description
BlkCart Black Cartridges
ColCart Coloured Cartridges
BlkCart Black Cartridges
ColCart Coloured Cartridges
BlkCart Black Cartridges
ColCart Coloured Cartridges
BlkCart Black Cartridges
BlkCart Black Cartridges
Nylon Balls of Nylon

If I filter for Black Cartridges, i get all the items but I also get Balls
of Nylon at the bottom of the filtered list. It appears as if the last item
in the list gets thrown in with the filtered data. As the ex. shows I only
have one entry for Nylon, but it does not show up in the AutoFilter drop-down
list. Neither does the (Blanks) and (NonBlanks) option. I selected the data
range first by just clinking in the headings for the data, then I tried again
by selecting the entire columns, and it still does not work.
There are no blanks within the data records, only the empty rows below.

I do have a couple VLOOKUP formulas and I add new entry either directly or
using a data form. I also have a couple macros to automatically open the
dataform and the filter.

Shereene

Thanks so much for taking the time.

 

[Dave Peterson]

I always put a blank row between my data and any other stuff on that worksheet.

And when I wanted to do filtering, I'd select my range first (excluding that
blank row and all the stuff under it).

Maybe it's as simple as adding that blank row (and removing and reapplying the
filter)????

 

[Shereene]

Hi Dave:

Good and bad news. I tried your suggestion of reapplying the filter but this
time by selecting the entire worksheet and it worked but only for a short
time. After I saved the file and tried to add an additional entry to the data
using a data form, it didn't work anymore. It went right back to not showing
the new item added or the (blanks) and (nonblanks) option in the filter
dropdown list. Saving the file and reapplying the filter didn't work after
that either.

How the worksheet is set up, there isn't any data below the main body of
data, so inserting a blank row wouldn't work.

It treats new items as not being part of the data. I think that problem lies
there.

Any other ideas would be very welcome

Shereene

 

[Dave Peterson]

Do you insert the new data and leave blank rows at the end of the original data?

If you do, stop it!

If you don't, good!

But for some reason, excel is not keeping track of what should be autofiltered.
(I don't think I've ever seen xl get confused if I just added more rows after
the last--no blank rows.)