auto transfer data in sheet2 (selected data)

[deen]

Hi all,


IN SHEET1 I HAVE DATA LIKE,

A B C D

1 CODE NAME AMT STATUS

2 12 JOE 1000 HIGH VALUE

3 13 JOHN 500 LOW VALUE

4 14 DEEN 1500 HIGH VALUE

5 15 JAMES 5600 HIGH VALUE

6 16 ANBIE 200 LOW VALUE



I NEED THE RESULT IN SHEET2, LIKE


A B C D

1 CODE NAME AMT STATUS

3 12 JOE 1000 HIGH VALUE

4 14 DEEN 1500 HIGH VALUE

5 15 JAMES 5600 HIGH VALUE

IN SHEET1 I WILL CLICK YHE COMMAND BUTTON(TRANSFER DATA TO SHEET2) IT
WILL AUTOMATICALLY PASTE IN SHEET2 ONLY(HIGH

VALUE DATA)

WITH USE OF MACRO IS ANY POSSIBLE OR THERE IS ANY FORMULA PLS LET ME
KNOW.



REGARDS,

DEEN

 

[Teethless mama]

Sheet2:
Header in row 1

In A2: =IF(ISERR(SMALL(IF(STATUS="HIGH
VALUE",ROW(INDIRECT("1:"&ROWS(STATUS)))),ROWS($1:1))),"",INDEX(INDIRECT(A$1),SMALL(IF(STATUS="HIGH VALUE",ROW(INDIRECT("1:"&ROWS(STATUS)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy across and down as far as needed