Copying specific rows *and* columns to a new table

I haven't used excel properly in a while and am trying to do something simple and failing.

As per the attached example, I have a large table with many columns. I want to keep this as a master and create a new table which only shows the data marked as 'completed' and only includes some columns.

I was trying a vlookup but, clearly, this doesn't work as there are several columns marked 'completed'. I was thinking of a pivot table but my brain will not extract the info I need to make that work either.

Can you help? The first sheet of the attached shows an example of an original table and the second sheet shows what I want it to look like.

Thanks

Glayva

Try the formula below in A3 of your "Aim" page. Confirm it with Ctrl + Shift + Enter instead of the usual Enter. Fill down through at least 20 rows, then fill right through column E. The result should look like your "Aim" sheet, but it'll automatically update as entries on your main page become 'completed'.

=IFERROR(INDEX(Original!$A$3:$F$23,SMALL(IF(TRIM(Original!$A$3:$A$23)="completed",ROW(Original!$A$3:$A$23)-2),ROW(1:1)),MATCH(A$2,Original!$A$2:$F$2,0)),"")

Or with two helper columns in a new sheet
Try this formulas in A3, B3 and C3, respectively, and copy down

Please Login or Register  to view this content.

Notes: 1) You can copy C3 down and right
2) In the new sheet on line two you must provide the name of the column or columns you want
See the file for clarification