Google Spreadsheet Filtering Data

Question:

( by Jonathan Hoover )

I have a list of all my books on one sheet categorized by author, genre, etc. I want to create different sheets for each genre so I can then sort by a secondary group (author or title or sub-genre). Is there a way to create these extra sheets and then enter in some sort of formula that will automatically populate a list from that category from the main genre list?

That way if I ever switch the genre on the main page, it will then update it on all other sheets so I don't have to go edit every sheet that book might be in.

In other words...if Sheet 2 is "Practical Theology", can I put in a formula on that sheet that will pull in all "practical theology" books from the main list on sheet 1? And any future practical theology books I add to it?

The first sheet on here is the complete list of my books. What I want to do is create a new sheet for every genre of books (Column D). 

So when I create a new sheet - for example - the first genre in column D - "Apologetics". I want to create an entirely new sheet for Apologetics. The reason I want to do this is so I can look at just Apologetics and then sort by the sub-genre which is Column E. 

I want to be able to repeat this for new sheets for each individual genre in column D.

Does that all make sense?

Thanks for the help!

Solution:

Have a look at the following screenshot:

I have the following formula in Cell A5:

=iferror(filter('Books'!A:F;'Books'!D:D=B1;'Books'!E:E=B2))

the above formula will give you all the columns mentioned by you in which there is First Genre which you can mention in Cell B1 and Sub Genre which you can mention in Cell B2.

I have set Data Validation in Cells B1 and B2, which will map data in 'Books'!D:D and 'Books'!E:E respectively.


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive