Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting answers from a list and displaying them
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting answers from a list and displaying them
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting answers from a list and displaying them
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting answers from a list and displaying them
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" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting answers from a list and displaying them
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting answers from a list and displaying them
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting answers from a list and displaying them
Brilliant!! Its perfect Thanks *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting data from columns and displaying them as rows | Excel Discussion (Misc queries) | |||
Selecting vertical rows and displaying them horizontally. | Excel Discussion (Misc queries) | |||
Multiple Answers From Drop Down List | Excel Worksheet Functions | |||
Selecting & Displaying An Array's Contents | Excel Programming | |||
Displaying answers that are off screen | Excel Programming |