copy and filter list from sheet1 to sht2

[squalo]

I have a list on sheet1.
The first column can be either true or false.

I would like sheet2 to show a list of all the rows that are true o
sheet1.


Thanks for any information

Pau

 

[Frank Kabel]

Hi
a non automatic way: Use 'Data - Filter - Advanced Filter' for this.
See:
http://www.contextures.com/xladvfilter01.html

 

[squalo]

Thanks

I appreciate your reply.
It worked but, I am looking for a dynamic link, instead of a one sho
type thing.

or maybe a button that would update sheet2.

As rows on sheet 1 are checked or unchecked (true or false) these row
either appear or disappear from sheet 2.

either on the fly or with a button

Thanks for looking at this

Pau

 

[Frank Kabel]

Hi
o.k. then try the following:
Assumptions.:
- col. A contains TRUE/FALSE
- col B+C are the values to copy

Enter the following array formula (with CTRL+SHIFT+ENTER) in A1 on
sheet 2:
=INDEX('sheet1'!B$1:B$1000,SMALL(IF('sheet1'!$A$1:$A$1000,ROW('sheet1'!
$A$1:$A$1000)),ROW(1:1)))
and copy this down / to the right

 

[squalo]

thanks a ton for your help
sorry to be clueless

what does CTRL+SHIFT+ENTER do?

it did copy the values from sheet1!b1:b2 to sheet2

but when I copy and paste the formula down column A of sheet 2 (or
across row 1 i get #NUM)

any ideas what I'm doing wrong?

Thank You
Paul

 

[squalo]

I googled ctrl+shift+enter and now the formula works perfect !

wow .....powerful stuff

you are great.

can't thank you enough !!!!!

Ciao

Pau

 

[Frank Kabel]

Hi
CTRL+SHIFT+ENTER creates an array formula. See:
http://www.cpearson.com/excel/array.htm


The error is expected if you don't have any matching values left. Try
the following to prevent this error:
=IF(ISERROR(INDEX('sheet1'!B$1:B$1000,SMALL(IF('sheet1'!$A$1:$A$1000,RO
W('sheet1'!
$A$1:$A$1000)),ROW(1:1)))),"",INDEX('sheet1'!B$1:B$1000,SMALL(IF('sheet
1'!$A$1:$A$1000,ROW('sheet1'!
$A$1:$A$1000)),ROW(1:1))))
also entered with CTRL+SHIFT+ENTER