• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula required to get the desired cell content

V

vinu

Member
Hello,


I have 2 sheets. In one sheet I have a dump data where I will filter based on requiremets. In the second sheet I used subtotal formula to get some fields some. For eg. In sheet1 I have country names coloumns. whenever I filter on any country I should get that country name in the 2nd sheet. Is there any way to get this though formula without usig VBA or Pivot.

(how to tell Excel to pick up the content of the next visible line/row cell of headig - it will give you my desired result).


Thanks in advance.
 
Vinu

I have used a helper column but have a workable answer without VBA or Pivot Tables


I setup some data like below starting in Sheet1 A1:

[pre]
Code: 
Country	Field	Helper
Aus	aa	1
NZ	aa	2
GB	aa	3
Aus	aa	1
NZ	aa	2
GB	aa	3[/pre]
Note that the helper column has a value in every row which is the same for each value of country


Try this on Sheet2

=OFFSET(Sheet1!$A$1,MATCH(SUBTOTAL(104,Sheet1!$C$2:$C$22),Sheet1!$C$2:$C$22,0),0)

Adjust ranges to suit


You can shift or hide the helper column and put a formula in it to make sure the values are always right
 
You must log in or register to reply here.
Back
Top