What's best to use? Macro or formula's?

**dandavis1** · 03-05-2010, 07:31 AM

Please see attached Document.

Basically from the "Overall" sheet I want to take all rows which have "closed" in column K and copy them on the "Closed Only" sheet. I just want to know what the best way would be and how to go about it.

Cheers...

**martindwilson** · 03-05-2010, 07:51 AM

use auto filter
auto filter on closed then copy paste to other sheet

**dandavis1** · 03-05-2010, 08:17 AM

There is going to be data added to the table every month and I need to try and take out every manual process (copying and pasting every month), as I am creating this for someone with little to no excel skills.

Is it possible to have some formulas sitting in the cells on the "Closed Only" sheet, for example =if(K10="Closed",(then copy that entire row) I would not know how to formulate the words in brackets.

Cheers,

**martindwilson** · 03-05-2010, 08:45 AM

well you could just record a macro doing the same thing then assign it to a button, however a formula solution is attached helper column M will do down to row 1100 change range if you want more.

**dandavis1** · 03-05-2010, 10:39 AM

Thanks very much for that. I have encorporated into my sheet, but I must have done something wrong, as it is returning #N/A.

See attached...

**martindwilson** · 03-05-2010, 11:48 AM

COLUMNS($A$1:A$1) not COLUMNS($A$1:$A$1)
and INDEX('Issues Summary'!$B$8:$J$1141 you need the whole range
also
MATCH("Closed "& a space is included there at the end of closed to match against "closed 1" not "closed1" (you could leave the space out if you changed the countif on the first sheet not to have it either)
so in B8
=IF(COUNTIF('Issues Summary'!$I$8:$I$1141,"Closed")<ROWS($A$1:$A1),"",(INDEX('Issues Summary'!$B$8:$J$1141,MATCH("Closed "&ROWS($A$1:$A1),'Issues Summary'!$K$8:$K$1141,0),COLUMNS($A$1:A$1))))