cell reference formula while filtering.

[shajan]

Hello Friends, Please help with a formula. I am trying to get the value of column C, 11th Row from top. When filtering the 11th row changes value, and I would want the changed value in another cell (C8).


Sample worksheet link is given below:


https://docs.google.com/open?id=0B3hQHIy6spPAMjc3NDYwOWMtNDNkMi00YTQ4LTgyNGEtYmQ1ZmEzZTJiOGE0


Thanks!

Shajan.

 

[Luke M]

I wasn't able to open your workbook, but here's how you could do it.

Create helper column D. In D2:

=SUBTOTAL(103,$C$2:C2)


This will create a running count of visible cells. Then, to get the value you want, you can use this formula:

=INDEX(C:C,MATCH(11,D:D,0))

 

[shajan]

thanks very much for your reply, i could not get the second part of it. I have posted the file here. http://www.4shared.com/file/MsGh_xFX/test_balance_sheet-1112.html

Thanks.

 

[NARAYANK991]

Hi Shajan ,


Use the following formula in cell C8 :


=INDEX(C11:C75,MATCH(SUBTOTAL(104,D11:D75),D11:D75,0))


Similarly , use the following formula in cell C7 :


=INDEX(D11:D75,MATCH(SUBTOTAL(104,D11:D75),D11:D75,0))


Narayan

 

[shajan]

Thanks a lot Narayan ! Your formula worked the way I wanted it. Will have to study the way this works.


Thanks to Luke too.

Shajan.