Hi there! I tried to include enough info to explain my problem without overloading you. The bold sentences is my question. Thank you in advance!
I've been attempting to build a formua that copies rows from one worksheet to another based on multiple criteria. I've attached a copy of my file with dummy data. My actual file has 900+ rows on the full list. Results are filtered to the new worksheet based on 3 categories and 4 options. Categories have values (blank), A, AB, ABC. Options have values (blank), (mix of +,V,E,G), --. The user can choose an option for a category, like B, and all rows with B in that category are copied. If an option is enabled, all rows containing text (+,V,E,G) are incuded, but all rows containing -- are not included, even if another option or category would otherwise include them.
I am using ISNUMBER(SEARCH()) for each of the categories to search if the user-defined category value matches.
I am attempting to use SUMPRODUCT for the options with the following arrays:
Option enabled true/false: {1,1,1,1} means all are enabled
Option has text (+,V,E,G,--): {1,1,1,1} means all have text
option does not have -- text: {1,1,1,1} means it does not have --, {-9,-9,-9,-9} means it does have --. (I explain the -9 below)
That SUMPRODUCT formua works if I write it by itself and apply it to a single row, but when I put it into the INDEX array formula, it doesn't work because the second array creates a 900x4 array rather than checking one row at a time. I'm not sure how to fix this. Please help!
Some more details:
The logic I am using to choose which rows should be copied to the filtered worksheet is
( Category 1 matches OR Category 2 matches OR category 3 matches OR Option 1 Adds OR Option 2 Adds OR Option 3 Adds OR Option 4 Adds ) AND (Enabled Option Does Not Remove)
I can't use logical OR/AND functions since this is an array formula, so I am using addition and multiplication to achieve this and wrapping it in a single IF statement. If I have a category match or an option adds a row, I add 1. If an option removes a row, I subtract 9. Then check IF(sum>0). This actually comes out as (Cat1 + Cat2 + Cat3 + (Opt1 + Opt2 + Opt3 + Opt4) where the possible values are 0, 1 or -9.
Bookmarks