Automatic transfer data to another sheet

[MOHD YUSOFF]

Hi all, i'm from malaysia and english is not my mother tongue. I will try my best to make you understand my problem.

I have a sheet that contains exam's result of my students including 'fail' and 'pass' status. I made another sheet as a report with the list of students that failed and passed the exam.

Can excel automatically transfer the data to sheet #2 when I put all the data in sheet #1?


I'll put an attachment as a sample.


I'm not so good in excel and never use VBA before.

Thanks a lot.

YUSOFF

[shukla.ankur281190]

Sheet Status Column B4 = IFERROR(INDEX(marks!$B$5:$B$11,SMALL(IF(LEFT(RIGHT($A$1,6),4)=marks!$D$5:$D$11,ROW(marks!$B$5:$B$11)-ROW(marks!$B$5)+1),ROWS(marks!$B$5:B5))),"") and drag it to down with Ctrl+Shift+Enter

Sheet Status Column E4 = IFERROR(INDEX(marks!$B$5:$B$11,SMALL(IF(LEFT(RIGHT($D$1,6),4)=marks!$D$5:$D$11,ROW(marks!$B$5:$B$11)-ROW(marks!$B$5)+1),ROWS(marks!$B$5:B5))),"") and drag it to down with Ctrl+Shift+Enter
4

For Pass

Please Login or Register  to view this content.

For Fail

Please Login or Register  to view this content.

Thanks
Ankur

[NeedForExcel]

Hi,

I dont think you should use VBA for a problem like this.

You can use formulas.

On the status sheet in cell A4 put =IFERROR(INDEX(marks!A$5:A$11,SMALL(IF(marks!$D$5:$D$11="fail",ROW(marks!$D$5:$D$11)-ROW(marks!$D$5)+1,""),ROWS(A$4:A4))),"") - Commit using Ctrl+Shift+Enter

On the status sheet in cell D4 put =IFERROR(INDEX(marks!A$5:A$11,SMALL(IF(marks!$D$5:$D$11="pass",ROW(marks!$D$5:$D$11)-ROW(marks!$D$5)+1,""),ROWS(D$4:D4))),"") - Commit using Ctrl+Shift+Enter


Drag down and across as required

[JohnTopley]

In A4

=IFERROR(INDEX(marks!$A$5:$A$11,SMALL(IF(marks!$D$5:$D$11="fail",ROW($A$5:$A$11)-ROW($A$5)+1,""),ROWS($A$5:A5))),"")

Inter with Ctrl+Shift+Enter

Change the range in red for range for name and mark.

Copy formula to columns for pass and change "fail" to "pass"

[NeedForExcel]

Also If you want a VBA Solution, then you can use this -

Please Login or Register  to view this content.

[MOHD YUSOFF]

thanks to all experts. I dont have any suitable words to express my appreciation. never think about iferror or vba. i will try all that anytime soon. And again, thank you very much .

YUSOFF.
Malaysia.