Dynamic Lists

[rhettblaine]

Hey guys,

I'm trying to create a dynamic list based on the user's selection of yes/no/NA answers. If the user selects No for one of the questions, the list should generate a message.

Example:

Questionnaire:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Question:[/TD]
[TD]Answer:[/TD]
[TD]Message if Answer = No[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Q3[/TD]
[TD]No[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Q4[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Q5[/TD]
[TD]No[/TD]
[TD]Purple[/TD]
[/TR]
[TR]
[TD]Q6[/TD]
[TD]No[/TD]
[TD]Black[/TD]
[/TR]
</tbody>[/TABLE]


Goal is to create list with messages if the Q is answered No:

• Green
• Purple
• Black

I've tried several different methods, but the part that I'm having the most difficulty is being able to recognize empty rows (Green, blank, Purple) to return the intended results.

Any suggestions?

Thanks!

 

[bsquad]

Assuming your "Question:" is in A1, "Answer:" is in B1...etc

Paste this formula in D2, make it an array - CTRL+SHIFT+ENTER so it has this crap around it {}, and fill it down; let me know if you have any issues or is not wat you are looking for

=IF(ROWS($D$1:D1)>SUMPRODUCT(--($B$2:$B$7="No")),"",INDEX(C$2:C$7,SMALL(IF(($B$2:$B$7="No"),ROW($C$2:$C$7)-ROW($C$2)+1),ROWS($D$1:D1))))

 

[rhettblaine]

bsquad... sending you a forum hug.

This worked brilliantly.

Where do I send the beer/flowers/fruit to??

Thanks!!

 

[bsquad]

lol, just hitting the 'Post Thanks' and 'Like' right under my username in the post would do just fine. If you need more of these just let me know I have a crap load of them. i.e. DISTINCT DUPLICATES, DISTINCT of DUPLICATES, IF the occurrence is > 1, tiebreaker logic...etc and all are dynamic