Return the filtered value into a specific cell

[Sal]

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.

 

[T. Valko]

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)

 

[Sal]

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!

 

[T. Valko]

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.

 

[Sal]

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.