Selecting answers from a list and displaying them

[Darin Kramer]

Hi there,

Within a workbook, three columns of data
Column 1 - Question number
Column 2 - Quest Description
Column 3 - Answer

There are 50 questions
Answers can only be yes or no

I need to know for all the NO answers, the question number and the
question description (in a neat format, ie one after each other)
I can write a normal If statement, but that will list the answers on
rows 1 to 50 (with blank lines inbetween where the answer is yes), I
need all the answers together somewhere else in the workbook, one after
each other...

Thanks and Regards



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bernie Deitrick]

Darin,

Using VBA, you can filter your data to show only "No" values, then copy
visible cells from the table and paste elsewhere.

Or you can use a table of rather complicated formulas to extract only the
values associated with "No" entries.

Do you have a preference?

HTH,
Bernie
MS Excel MVP

 

[Darin Kramer]

Hi Bernie,

My preference is for the one that requires the least user input, ie
hopefully they wont have to do much (including clicking a button to run
a macro - they tend to get things wrong!) What worries me is if you are
saying they are complicated forumlae, then Im in trouble!!!

Lets perhaps give the formulae a try - if I cant make head or tail of
it, I will try the VB...

Thanks for your help.

Regards

Darin


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bernie Deitrick]

Darin,

OK. Assumptions:

Your sheet with the questions is named "Question Sheet" without the quotes.
Your numbers, descriptions, and answers are in columns A, B, and C.
Answers can only be Yes or No
Row 1 has headers on both sheets.
Rows 2 through 51 have the 50 questions and answers.
Your "somewhere else in the workook" is another sheet, with headers in row
1, and the table starting in row 2, of columns A, B, and C.

On your other sheet, in cell A2, array enter the formula (enter with
Ctrl-Shift-Enter)

=IF(COUNTIF('Question
Sheet'!$C:$C,"No")>=ROW()-ROW($A$2)+1,INDIRECT("'Question Sheet'!" &
ADDRESS(LARGE(('Question Sheet'!$C$2:$C$51="No")*ROW('Question
Sheet'!$C$2:$C$51),COUNTIF('Question
Sheet'!$C:$C,"No")+ROW($A$2)-ROW()),COLUMN()-COLUMN($A$2)+1)),"")

Copy that cell to cells A2:C51, and you're done.

IF you can't get it to work, email me privately, and I will send you the
working example.

HTH,
Bernie
MS Excel MVP

 

[Darin Kramer]

Hi Bernie,

Thanks for the formulae I got it to work. Only problem is that whenever
I enter information anywhere else in the workbook it calculates cells,
which takes some time, and i think may be distracting for the user. I
thought of turning calculation off, but I need it on for other parts of
the workbook. As such, Can I ask if you could send me the VB - think I
will build it in, then just ask user to click a button to run Macro to
get listing of no answers.

I appreciate your assistance!

(I didnt mail u privatgely, cause I dont have your email address

Thanks and regards

Darin




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bernie Deitrick]

Darin,

Same assumption, with one additional: your questions and descriptions with
"No" values will go onto a worksheet named "No Answers" starting in cell A1.

Sub Macro1()
Sheets("No Answers").Range("A1:C51").ClearContents
With Sheets("Question Sheet").Range("A1:C51")
.AutoFilter Field:=3, Criteria1:="No"
.SpecialCells(xlCellTypeVisible).Copy _
Sheets("No Answers").Range("A1")
.AutoFilter
End With
End Sub

HTH,
Bernie
MS Excel MVP

 

[Darin Kramer]

Brilliant!!

Its perfect

Thanks


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!