Extract from A List

[joec]

I would like to extract from a Master List of Books those titles that
have not yet been purchased which are identified by a blank cell in a
column headed "Bought". A "yes" indicates that the have been bought. At
the moment all titles that have not been bought are conditional
formatted with a yellow background. I would like the extracted list to
appear on a seperate worksheet.
Is this possible to achieve without copy and paste, and how can it be
done so that the list keeps up to date automatically?
Any help would be most welcome.

 

[phildeaves]

Is this possible to achieve without copy and paste, and how can it be
done so that the list keeps up to date automatically?
Any help would be most welcome.

I suppose one way you could do this is:

* Create a new sheet
* Fill it with formulae to duplicate the contents of the first sheet
* Put a filter on the "Bought?" column to display only those entries
with blank cells
* Periodically "unfilter" and filter again to pick up changes.


The experts may have a better way to do this: I'm merely a user.
However, if all else fails, I hope this is of some help to you.

PhilD

 

[Domenic]

Assumptions:

On Sheet1...

A1100 contains your data

First row contains your headers/labels

Column A contains the 'Book Name'

Column B contains the 'Author'

Column C contains the 'ISBN'

Column D contains 'Yes' when a book is bought

Formulas:

On Sheet2...

A1: enter a 0 (zero)

A2, copied down:

=IF((Sheet1!A2<>"")*(Sheet1!D2=""),LOOKUP(9.99999999999999E+307,$A$1:A1)+
1,"")

B1:

=LOOKUP(9.99999999999999E+307,A:A)

C2, copied down:

=IF(ROWS(C$2:C2)<=$B$1,MATCH(ROWS(C$2:C2),$A$2:$A$100,0),"")

D2, copied down and across:

=IF(N($C2),INDEX(Sheet1!A$2:A$100,$C2),"")

Note that if you prefer, you can always hide helper Columns A, B, and C.

Hope this helps!

 

[Bob Greenblatt]

Assumptions:

On Sheet1...

A1100 contains your data

First row contains your headers/labels

Column A contains the 'Book Name'

Column B contains the 'Author'

Column C contains the 'ISBN'

Column D contains 'Yes' when a book is bought

Formulas:

On Sheet2...

A1: enter a 0 (zero)

A2, copied down:

=IF((Sheet1!A2<>"")*(Sheet1!D2=""),LOOKUP(9.99999999999999E+307,$A$1:A1)+
1,"")

B1:

=LOOKUP(9.99999999999999E+307,A:A)

C2, copied down:

=IF(ROWS(C$2:C2)<=$B$1,MATCH(ROWS(C$2:C2),$A$2:$A$100,0),"")

D2, copied down and across:

=IF(N($C2),INDEX(Sheet1!A$2:A$100,$C2),"")

Note that if you prefer, you can always hide helper Columns A, B, and C.

Hope this helps!

Why create a whole new sheet? Just filter the list. A simple autofilter will
do exactly what you want.

 

[PhilD]

Why create a whole new sheet? Just filter the list. A simple autofilter will
do exactly what you want.

100% true, but the original question stated "I would like the extracted
list to appear on a seperate worksheet".

PhilD

 

[Domenic]

Why create a whole new sheet? Just filter the list. A simple autofilter will
do exactly what you want.

Actually, the OP wants the list updated automatically and on a separate
sheet.

The formula system will do just that without the need to go through the
filter process each time a new and updated list is needed.

But, yes, there's no reason why one couldn't use Autofilter or Advanced
Filter. As you said, it's still a fairly simple process.

 

[joec]

Many thanks to all the guys who responded and contributed. I'm sure all
your suggestions will be most helpful.
JoeC

 

[joec]

Thanks Domenic.
Your suggestion works a treat especially with the auto update.
Best Regards
JoeC

 

[Domenic]

You're very welcome! Glad I could help!

Cheers!