Excel Filters

[Nikki]

Hi

I have 12 worksheets labelled months of the year. On each sheet I have 8
tables listed like this.

Purchase Sales
Purchase Sales
Purchase Sales
Purchase Sales

Each table is for a different grain commodity (4 in total)
I have added filters and subtotals so I can drill down further to see what
different types of commodity and loads are left. However, I would like to
filter the purchase and sales separately but as they are side by side, when I
filter purchase the sales side shrinks too. Is there any way I can filter
the purchases and the sales side stays as it is? And vise versa.

Many thanks for your help.

 

[jlclyde]

Hi

I have 12 worksheets labelled months of the year.  On each sheet I have 8
tables listed like this.

Purchase                 Sales
Purchase                 Sales
Purchase                 Sales
Purchase                 Sales

Each table is for a different grain commodity (4 in total)
I have added filters and subtotals so I can drill down further to see what
different types of commodity and loads are left.  However, I would like to
filter the purchase and sales separately but as they are side by side, when I
filter purchase the sales side shrinks too.  Is there any way I can filter
the purchases and the sales side stays as it is?  And vise versa.

Many thanks for your help.

You could make them into lists instead of filters. First off you need
excel 2003 or above. Then hightlight a group that you want to filter
and right click and select List. If your columns have headers check
box. There is a little grey triangle in the bottom right so you can
resize this list to include and not include data. You can also set up
many lists all over the place.

Jay

 

[Nikki]

Many thanks for your help. I have done what you suggested but when I filter
through the list on the Purchase side, the Sales side also shrinks but I need
that one to stay as it is so that I can then filter it seperately to compare
data.

Any other suggestions

 

[Gord Dibben]

That's how filter works.

It hides rows.

Lists do not become separate filterable ranges not affecting adjacent columns.

One solution would be to copy the worksheet to a new workbook.

Arrange two windows side by side and you can filter on Purchases on one book and
sales on other book.


Gord Dibben MS Excel MVP

 

[Nikki]

Many thanks for that.

That's how filter works.

It hides rows.

Lists do not become separate filterable ranges not affecting adjacent columns.

One solution would be to copy the worksheet to a new workbook.

Arrange two windows side by side and you can filter on Purchases on one book and
sales on other book.


Gord Dibben MS Excel MVP