Return the filtered value into a specific cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sal - 27 May 2008 18:55 GMT

I haven't figured out how to return the filtered value into a specific cell.
I have a large spreadsheet where I filter 3 specific columns and I need those
3 specific filtered values (which are text) to be display in a different area
of the same spreadsheet in a specific cell. It think it may be similar to
the subtotal function which only adds the filtered values =subtotal(9,Range)
but I've been searching only with no success yet. Thanks.

Reply to this Message

T. Valko - 27 May 2008 19:02 GMT

Try this array formula** :

Assume row 1 is the column header with filter.

A2:A15 is the actual data range.

=INDEX(A2:A15,MATCH(1,(SUBTOTAL(3,OFFSET(A2:A15,ROW(A2:A15)-MIN(ROW(A2:A15)),0,1)))*(A2:A15<>""),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Signature

Biff
Microsoft Excel MVP

>I haven't figured out how to return the filtered value into a specific
>cell.
[quoted text clipped - 6 lines]
> =subtotal(9,Range)
> but I've been searching only with no success yet. Thanks.

Reply to this Message

Sal - 29 May 2008 00:03 GMT

Excellent! It works! Now I need to figure out how to modify the function so
when the spreadsheet is not filtered for any specific value to return a
blank, or only return a filtered value as long as all of the values in that
column are the same. If I can't figure it out, then you'll be hearing from me
again. THANK YOU!

> I haven't figured out how to return the filtered value into a specific cell.
> I have a large spreadsheet where I filter 3 specific columns and I need those
> 3 specific filtered values (which are text) to be display in a different area
> of the same spreadsheet in a specific cell. It think it may be similar to
> the subtotal function which only adds the filtered values =subtotal(9,Range)
> but I've been searching only with no success yet. Thanks.

Reply to this Message

T. Valko - 29 May 2008 04:39 GMT

>I need to figure out how to modify the function
>so when the spreadsheet is not filtered for any
>specific value to return a blank

You could compare the number of visible rows to the number of unfiltered
rows:

=IF(SUBTOTAL(3,A2:A15)=ROWS(A2:A15),"",...........

If the table is unfiltered then the subtotal will equal the numbers of rows.

Signature

Biff
Microsoft Excel MVP

> Excellent! It works! Now I need to figure out how to modify the function
> so
[quoted text clipped - 16 lines]
>> =subtotal(9,Range)
>> but I've been searching only with no success yet. Thanks.

Reply to this Message

Sal - 29 May 2008 18:28 GMT

I got lost in your subtotal function. I have attached a small section of the
spreadsheet. At the top is where I need to display the 3 results, however
when I filter by a specific variety then I don't want the grower to be
displayed if more than one grower has that same variety and the same applies
for the pool. Sometimes I just filter by grower or pool. Basically I only
want it to return that value as long as all the visible values with a column
are the same. Hope I'm not confusing you.

    Variety    Grower    Pool
    R    WY00    0XX1


DATE    VAR    GWR    POOL    PACK
8/16/07    R    WY00    0XX1    TP
8/17/07    GG    WY00    0JH1    TP
8/17/07    FUJI    AK00    0XX1    TP
8/17/07    BR    AK00    0XX1    TP
8/17/07    GG    G800    0JH1    TP
8/17/07    FUJI    TC00    0XX1    TP
8/17/07    GG    IP00    0PK1    TP
8/17/07    JG    WY00    0XX1    TP
8/17/07    GG    AK00    1KK1    TP
8/17/07    GG    WY00    0XX1    TP
8/17/07    FUJI    AK00    0XX1    TP
8/17/07    GG    G800    1KK1    TP
8/17/07    JG    AK00    0PK1    HTP
8/17/07    G    G800    0XX1    HTP
8/17/07    GG    TC00    0XX1    HTP
8/17/07    JG    WY00    1KK1    HTP
8/17/07    G    G800    0PK2    HTP
8/17/07    BR    TC00    0XX1    HTP
8/17/07    BR    AK00    0RR1    HTP
8/21/07    GG    AK00    0XX1    TP
9/4/07    R    8888    8888    HTP
9/4/07    R    8888    8888    HTP

Thank you.

> >I need to figure out how to modify the function
> >so when the spreadsheet is not filtered for any
[quoted text clipped - 27 lines]
> >> =subtotal(9,Range)
> >> but I've been searching only with no success yet. Thanks.

Reply to this Message