-1

I've created a template to track music cues written for a TV show and now I'm trying to work out a few kinks in its functionality. The end use of this template includes regularly adding rows to each episode (or "group" of rows), and I'm trying to make it as simple as possible to use, without the user having to do extra work besides inserting a new row and the data for F:J.

Is there a better way to structure this so that I can filter a single Writer while maintaining the multi-row groupings?

I've been learning this stuff pretty rag-tag, so perhaps some of this template isn't how it might usually be structured by a pro. Any structural suggestions would be really appreciated.

  • The linked spreadsheet is now on the owner trash. Please recover it or that enough details to the question itself. Perhaps a screenshot will be enough in this case. By the other hand question on this site should be specific meaning one question per post, otherwise the question is too broad. – Rubén Jul 18 '18 at 21:30
  • I've recovered it, however my question was never really answered. I am updating my question with details on what I ended up doing (which that old file doesn't reflect). – etudes Jul 19 '18 at 22:01
  • If you are adding the solution, add it as an answer. – Rubén Jul 19 '18 at 22:03
  • 2
    I'm still open to a better answer than what I figured out, but I am marking this as solved with the answer I just posted, as it works out well enough. – etudes Jul 19 '18 at 22:09
1

Short answer

  1. Use SUBTOTAL, INDIRECT, ROW, SMALL, IF, ISTEXT and ArrayFormula to calculate the number of visible "writers" by "episode".
  2. Filter by an specific "writer"
  3. Hide the "episodes" with no "writers".

Alternatively use macros to speed the filtering procedure (steps 2 and 3).

Explanation

If you don't mind to do a two step filtering add the following formula to the cell corresponding to first "header" and column K (K2)

=ArrayFormula(SUBTOTAL(3,INDIRECT("K"&ROW()+1&":K"&SMALL(IF(ISTEXT($K3:$K),,ROW($K3:$K)),1)-1)))

then copy K2 to all other cells that corresponds to the headers and column K. This will make that the relative part of $K3:$K be adjusted automatically.

The formula will return the visible number of writes for each episode.

The above should be done once by each episode.

To filter, first select the writer name. Let say Person 5

After filtering, the number of writers for each episode will be updated. To hide the episodes with no writers belonging to the filter criteria, filter 0.

Additionally you could create macros to do the above faster. The following is an example of a macro that shows episodes with Person 5.

/** @OnlyCurrentDoc */

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues(['Person 1', 'Person 2', 'Person 3', 'Person 6'])
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(11, criteria);
  criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues(['0', 'Person 1', 'Person 2', 'Person 3', 'Person 6'])
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(11, criteria);
};
  • That is a very clever solution, thank you! Not sure if I should mark your answer as the solution? Regarding the macro, I'm quite unfamiliar with them, and it seems to be yielding an error: "Cannot call method "setColumnFilterCriteria" of null." Plus, with the actual amount of third party writers we list in real usage (some of which are one-time only and some of which are still being added), needing to update the macro with every new person listed as HiddenValues doesn't seem as effective as using the filtering trick. – etudes Jul 21 '18 at 3:44
  • @etudes Thank you for you nice words. Regard the green tick mark, it means "accepted answer". You could accept the answer that you think that best answers the question and you could change the accepted answer if you change your mind. Regarding the macro the purpose to include it on the question it just to mention that macros could be helpful, but for the reasons that you mentions perhaps a simple like the one included in the answer its not helpful for the reasons that you mention and something more elaborated should be used. – Rubén Jul 21 '18 at 13:17
1

Here is a new version of my template for anyone interested. I got rid of merged cells and spacer rows and let each episode have its own row as a "header". This makes adding new music cue rows easier without having to retype the episode info for every cue, while maintaining visual contrast and clarity. I also use Google Sheets' new row grouping feature to allow for expanding/collapsing of completed episodes.

Filtering is still imperfect, but it allows for a decent medium. Filtering by the Writer column and including Blanks allows for proper filtering by a given writer, but it unfortunately still leaves every episode header row visible regardless if that writer is featured.

0

Filter and sort built-in function works better when the data has an structure like the following

The above structure also allow us to create a Pivot Table

The Pivot Table Editor has controls to sort rows, columns, filter values, and more.

  • My original goal was to avoid that exact structure you mention, because it's more difficult to visualize and contrast episode to episode, even with conditional formatting. I don't see how a Pivot Table will give me a clear list of all cues written by a given writer, though? It seems just as effective to filter the original sheet by the Writer column with that structure. Side note, I am adding a data-neutralized version of my new template in the answer I posted. – etudes Jul 20 '18 at 22:37
-1

I played with your spreadsheet a bit and I have one main suggestion on how to improve it. Find a way to avoid using merged cells. Generally merged cells will interfere with sheet functionality. You can usually achieve the same look by just changing cell colors and cell outlines. If you make a new version w/o merged cells, I can show you a few tricks to maintain your formatting on new entries.

  • 1
    Agreed. let me make this a comment instead of an answer. – Jimmy Nov 5 '17 at 9:29
  • is there an easy way to do so? – Jimmy Nov 5 '17 at 9:29
  • I'm down to try alternate solutions. Here is an updated version without merges. It makes it a lot less visually appealing or concise, but if you have suggestions to match the original more, I'd love to hear. For instance, ideally the episode name doesn't have to be copied down every single time a new cue is entered for that episode. Cues with more than one writer also means duplicate rows of the same cue. Thanks for the help! – etudes Nov 6 '17 at 4:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.