How to make this formula working in another sheet(Noob question) example added.plz help.

[nicholes]

please download the example file and colomn D E F G data should go in a new sheet. i could not do it.

and also please tell me why this file is asking to save changes every time even when i have not made any changes in it ?

[benishiryo]

hi nicholes. INDIRECT is 1 of the volatile formulas, hence it calculates when you open the file and ask if you want to save.
http://www.decisionmodels.com/calcsecretsi.htm

try using this in D3:
=IFERROR(INDEX($A$3:$A$13,SMALL(IF($B$3:$B$13=$D$1,ROW($A$3:$A$13)),ROW(1:1))-ROW($A$3)+1),"")
CTRL + SHIFT + ENTER still

to transfer to another sheet:
1. select D1:H7
2. CTRL + X to cut (not copy)
3. create a new sheet and paste wherever you like

[nicholes]

This is exactly what i need

Thanks for the help i added reputation for you also

[nicholes]

BTW can you please throw some light on the formula. actually a long time ago someone give me that formula and i dotn understand this one

[nicholes]

hi nicholes. INDIRECT is 1 of the volatile formulas, hence it calculates when you open the file and ask if you want to save.
http://www.decisionmodels.com/calcsecretsi.htm

try using this in D3:
=IFERROR(INDEX($A$3:$A$13,SMALL(IF($B$3:$B$13=$D$1,ROW($A$3:$A$13)),ROW(1:1))-ROW($A$3)+1),"")
CTRL + SHIFT + ENTER still

to transfer to another sheet:
1. select D1:H7
2. CTRL + X to cut (not copy)
3. create a new sheet and paste wherever you like

that works when i paste this formula only in first row of any column in a new sheet .when i paste this formula in some other row (for example in 10th row) it does not work.

also please note that i want to work with D column content so only concentrate on it. thanks for the kind help.