Create a list for A1:A11 with title "toplist" in A1

Create a list for A12:A21 with title "bottomlist"

In D1 enter this formula.

=SUBTOTAL(4,A1:A11)*SUBTOTAL(4,A12:A21)

This picks the MAX from each filtered list, which will be the only visible
number.

Filter top list for 7

Filter bottom list for 8 to return 56 in D1

Closest I can come right now.


Gord


On Thu, 25 Sep 2008 08:40:12 -0700, Youssef B.
wrote:

Hi guys,
thanks for the input.

Gord, I feel youre getting close, but to be more specific, the following is
an example of what I would like to acheive:

A B C D
1 1
2 2
3 3 D3=A1*B1
4 4
5 5
6 6
7 7
8 8
9 9
10 10

This is a simple example using the multiplication table. I would like to
filter both columns A and B independently, so that my formula can be used to
determine any combination of A*B (ie. 5*6 or 3*9 or 7*1 etc...).
Is this possible with Excel, or am I wasting my time trying to figure this
out?


"Gord Dibben" wrote:

The filtering on column A hides the rows in B also so no can do as you wish.

If using 2003 or newer you could turn column A and column B into two
distinct "lists" unser DataListCreate List.

These can be filtered independently.


Gord Dibben MS Excel MVP

On Wed, 24 Sep 2008 07:55:02 -0700, Youssef B. <Youssef
wrote:

Hi,
I was wondering if anyone had any advice on filtering columns independently.
I have the following data:

A B

1 3
2 8
3 8
4 6
5 9

Basically, I would like to filter column A independently of column B.
I'd like to to filter column A to show only A4 (for example) and still be
able to filter column B by any criteria I like. Normally, If I filter column
A by A4, Excel only shows the value from B4 in the B column.
Any suggestions would be appreciated.