Simple problem: I have a sheet that looks like
NUM TYPE TITLE
001 book Call Of The Wild
002 DVD Pulp Fiction
003 DVD Dirty Harry
004 book Cloud Atlas
005 DVD Some Like It Hot
006 book Moby Dick
What I would like is two additional separate worksheets entitled Books and DVDs which contain the data filtered by the TYPE column.
e.g. a separate sheet called Books like this (and similar for DVDs)
NUM TYPE TITLE
001 book Call Of The Wild
004 book Cloud Atlas
006 book Moby Dick
Easy, copy the data and apply a filter but this is static so if I add a new book to the main sheet then it doesn’t show up on the Book sheet unless I remove and reapply the filter. I want the Book and DVD sheets to automatically reflect the data as entered on the main master sheet, no buttons, no refreshes, just a subset of the data where TYPE=‘book’
I’ve web searched this a LOT of times and find all sorts of weird and wonderful thoughts, some of which I understand and don’t seem to work, some which nearly do what I want but are not explained well enough for me to grok.
As a programmer if this were a database it would be trivial.
Oh, of course, the real-world data I’m trying to display is far more complex but if I can get insight on how to do it for such a simple case then I’m sure the rest is easy.
OK let’s add to this to clarify. The request for this came in from a client - my wife! What she would would like is the 3 sheets Master (with everything on), Books and DVDs, and ANY change made to ANY sheet automatically, without any button presses or manual refreshes propagated to other sheets. For example if she adds a new book to the Master sheet then she wants this to appear on the Book sheet. If she adds a new book to the BOOK sheet she wants this to appear on the MASTER sheet - all automatically.
This is trivial in programming I knock up a SQL database and a bit of Python and a few appropriate windows in a GUI and it is trivial. She wants to use a spreadsheet so she can share the data (and editing) with others and believes that this is something that ought be easy. I have many years of programming experience but ZERO with spreadsheets and can’t find a simple example of something similar enough that I can grok the model required.
I know that sheets are static so I’m going to need a macro/set of macros that trigger when a change is made but I just can’t find anything anywhere that will point me in a direction to get started.