We have a sheet with a column that allows multiselect of the drop down options. We need to count how many times an item has been selected but HAS only works if they select just the one option and CONTAINS grabs options that happen to appear inside other options. For example: Using CONTAINS({range},"apple") with a list that has apple and Snapple as options will count 13 Snapples and 23 apples even if there are only 10 apples. Using HAS({range},"apple"), if they select Snapple and anything else it won't find it at all.
We found a way to parse the multiselect so that each selection appears alone in a column, then query that grid, but they could theoretically choose over a dozen items off the list and we would need to add more than a dozen columns to parse into. The log sheet is already very, very wide and we don't want to add more columns to it. We tried to create a reference to the column in a metrics sheet and parse it there but can't figure out how to reference each row in the entire column without creating a direct reference to each cell, which would be repeated in each formula up to 3 times. (We discovered that the selections are separated by Char(10) line feed but only when multiples are selected.)
Option 1: Duplicate the column by reference into another sheet, then use formulas that reference that column in the same sheet. Is this possible? If so, how?
Option 2: Create a reference to the cross sheet column like in Excel $Sheet$Column1 where 1 is the row and increments as I copy the formula down the rows of the calling sheet or create a column in the Metrics sheet that exactly replicates the original. Then I can create a formula in each column referencing that cell in the row instead of making 13+ cross sheet references on each row.
Option 3: manually copy and paste the column contents every time we want to update the dashboard. This is not an ideal solution unless we can automate it and have a button on the dashboard/report to regenerate the statistics.
Option 4: Make the selections absolutely unique, such as add a period to the end of each one, and then go through and clean up the data that already exists. Not impossible but not ideal.
What would make this much easier is if there were a Parse() and ForEach() function so that we could create a single formula that would loop through each cell in a range, parse that cell content on the provided delimiter, then do CountIf on the array using HAS(). Since I know there isn't either one, what ideas can anyone suggest?
