Dynamic Checklists with Multiple Criteria

1
votes

I have a dynamic checklist that changes the questions displayed based on multiple criteria. It works, but I'm hoping there is a more efficient way, especially with increased criteria.

Example:

Let's say there are 2 criteria that can be either "Y" or "N". The parameters on Checklist A are set such that only the questions that have Criteria1=Y and Criteria2=N should be shown.

Question1 is [Y,N] and should be shown.

Question2 is not [Y,N] and should NOT be shown.

Question3 is not [Y,N] and should NOT be shown.

Question4 is [Y,N] and should be shown.

Therefore, Checklist A shows:

Question1

Question4

One following the other, like Question2 and Question3 didn't exist.

I currently have it working using helper columns counting, (the parameter matches), for each possibility (Y/Y, Y/N, N/Y, N/N). Then doing a lookup for each row. (In the example above, for the second question in Checklist A, it does a lookup on the "Y/N" column based on the '2' and pulls Question4 for 2nd 'displayed' question).

It works well, instantly 'manufacturing'/changing 200 row checklists based on the parameters. However, I'm hoping there is a better way than creating columns to count all possible combinations.

Is there some formula combination to determine that the second combination of Y/N is row 4? It's almost like => What row is it that the COUNTIFS(for specified parameters) = 2?

Maybe a rows/countifs combination? Some array formula/SUMPRODUCT?

1
dynamiclookup
May you please show some of your code? - Sweet Chilly Philly
There isn't any code, only Excel functions, (mostly lookup functions). It's more about the structure, (using helper columns to determine multiple criteria matching counts). I'm looking for an elegant way to determine the location of the nth match, (of the multiple criteria), that doesn't require helper columns. Seems like SUMPRODUCT should be able to do it, but I haven't figured out how. - tomtom42

1 Answers

0
votes

I figured it out using INDEX, SMALL and COUNTIFS:

IF(A13=0,"",INDEX(MAIND,SMALL(IF(COUNTIFS(A13,INDEX(MAIND,,MATCH(A13,NCT,0)),B13,INDEX(MAIND,,MATCH(B13,NCT,0)),C13,INDEX(MAIND,,MATCH(C13,NCT,0)),D13,INDEX(MAIND,,MATCH(D13,NCT,0))),BaseQuestions!$A$1:$A$316,""),COUNTIF(B$12:B13,B13))+ROW(CAQ)-1,COLUMN(IQ)))

The parameters are in the A,B,C & D columns, starting at row 13. MAIND is a range name where the Questions and Include/Exclude toggles exist. NCT is the headers to determine which column in MainD to look. CAQ is the top of the Question list. IC is the column containing the questions.

The COUNTIFS count the rows in MainD where toggle listing all match the parameters in Columns A, B, C & D. Each column in the toggle listing contains either the parameter value, (if it should show that question) or "xx", (which really can be anything <> to the parameter value). The Small function then picks the nth question that matches all the criteria, (the parameters in A-D) where n is the number of parameters in A-D matching. Therefore, in the 9th row of the dynamic questionnaire it shows the question from the row in MainD that is the 9th row of MainD that matches A-D.