I have been putting together a spreadsheet in Google Sheets that includes various data/information relating to the hundreds of characters in a novel I am reading. Two of the columns relate to the chapters that a given character, respectively, 1) actually appears in, and 2) is just mentioned in. (The data within this column, while technically numerical, is formatted as plain text.) As a way of allowing a user more easily to find the row for a specific character that they come across while reading, I have been attempting to utilize the new Google Sheets feature of filter views to show only rows/characters who appear or are mentioned within different ranges of chapters ("acts").
The other day, I discovered that filter views (and filters in general) are cumulative, so if I specify certain sets of chapters to display within the "Appearances" column, when I go to specify the sets to display within the "Mentions" column, I can only select those in rows whose "Appearances" values were specified.
I want a filter view to display rows for characters who appeared OR were mentioned in a given range of chapters. But filter views operate under "AND" logic. Is there a way to get filter views to display all of the rows I want them to, given my parameters? Or is there an alternate method of accomplishing this, perhaps using formulae?
(I did scour Google's documentation for possible alternatives---the REGEXMATCH formula seemed particularly useful for my purpose, though I am not sure how I would implement it.)