Return the filtered value into a specific cell
 

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.

Answer: Return the filtered value into a specific cell
 

1. Select the cell where you want to display the filtered value.
2. In the formula bar, type =SUBTOTAL(3,Range) where Range is the range of cells that you filtered.
3. Press Enter to calculate the subtotal.
4. Now, use the OFFSET function to return the filtered value into a specific cell. In the formula bar, type =OFFSET(Cell,0,0) where Cell is the cell where you typed the SUBTOTAL function.
5. Press Enter to display the filtered value in the specific cell.

For example, let's say you filtered columns A, B, and C and you want to display the filtered value in cell E1. Here are the steps:

1. Select cell E1.
2. In the formula bar, type =SUBTOTAL(3,A:C) and press Enter.
3. In the formula bar, type =OFFSET(E1,0,0) and press Enter.

Now, cell E1 will display the filtered value. You can repeat these steps for the other two filtered values and display them in different cells.

Return the filtered value into a specific cell
 

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,RO W(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)

--
Biff
Microsoft Excel MVP


"Sal" wrote in message
...
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.

Return the filtered value into a specific cell
 

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!

"Sal" wrote:

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.

Return the filtered value into a specific cell
 

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.


--
Biff
Microsoft Excel MVP


"Sal" wrote in message
...
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!

"Sal" wrote:

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.

Return the filtered value into a specific cell
 

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.


"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"Sal" wrote in message
...
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!

"Sal" wrote:

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.