How to use autofilter in excel with formula reference changing

[Marcio]

Hi,
I have a table
A B C
1 forro 120
2 ment 80 40
3 forro 50 30
4 ment 40 10
5 ment 30 10
6 forro 20 10
7 forro 10 10
8 forro 5 5



Starting from C2, the column C has a formula (=B1-B2) result = 40; (=B2-B3)
result = 30; etc...

When I apply autofilter the formula in cell C keeps the original information
(=B1-B2), and I would like to have a formula to change and shows de result as
below (=B1-B3) result = 70; (=B3-B6) result = 30; etc...
A B C
1 forro 120
3 forro 50 70
6 forro 20 30
7 forro 10 10
8 forro 5 5

The objective is to have a formula considering just the visible cells.

Thank you,
Marcio

 

[T. Valko]

This seems overly complex but it works...

Array entered in C2 and copied down as needed.

=INDEX(B$1:B$8,MATCH(SUBTOTAL(3,B$1:B1),SUBTOTAL(3,OFFSET(B$1:B$8,,,ROW(B$1:B$8)-ROW(B$1)+1)),0))-INDEX(B$1:B$8,MATCH(SUBTOTAL(3,B$1:B1)+1,SUBTOTAL(3,OFFSET(B$1:B$8,,,ROW(B$1:B$8)-ROW(B$1)+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.