Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a Questinaire with filtered responses
I have produced a simple questionaire in excel i.e. 20 questions, with a
reponse predefined as YES or NO. In another sheet "Report-calcs" it will list the answers for the user. The following basically checks for NO and then if no gives the predefined answer to the question =IF('4 Data Quality'!C7="No",'4 Data Quality'!E7,0) C7 = the response, E7 = a hidden answer to the question In my "report page" I have this formula on around 100 lines Problem with this if the answer was YES it would have an empty space - Question - how to I create a questionaire, with only answers to "NO" being shown in a report (blank or YES elements removed) The report will probably have 5 or 6 sections (i.e. H & S, DDR, DQ) with each section having predefined answers. I thought about Vlookup but not sure how to make it do this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a Questinaire with filtered responses
Instead of returning a zero in your formula, you could return a blank:
=IF('4 Data Quality'!C7="No",'4 Data Quality'!E7,"") Then, by applying Autofilter to the column with this formula in (choosing Non-blanks from the fulter pull-down), you can hide all the questions which relate to a Yes answer. Hope this helps. Pete On Aug 26, 12:15*pm, EddS wrote: I have produced a simple questionaire in excel i.e. 20 questions, with a reponse predefined as YES or NO. In another sheet "Report-calcs" it will list the answers for the user. The following basically checks for NO and then if no gives the predefined answer to the question =IF('4 Data Quality'!C7="No",'4 Data Quality'!E7,0) C7 = the response, E7 = a hidden answer to the question In my "report page" I have this formula on around 100 lines Problem with this if the answer was YES it would have an empty space - Question - how to I create a questionaire, with only answers to "NO" being shown in a report (blank or YES elements removed) The report will probably have 5 or 6 sections (i.e. H & S, DDR, DQ) with each section having predefined answers. I thought about Vlookup but not sure how to make it do this. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a Questinaire with filtered responses
Pete - thanks - I have done that but this still requires user intervention to
filter the report - this will cause errors with people forgetting what to do. I'll keep trying "Pete_UK" wrote: Instead of returning a zero in your formula, you could return a blank: =IF('4 Data Quality'!C7="No",'4 Data Quality'!E7,"") Then, by applying Autofilter to the column with this formula in (choosing Non-blanks from the fulter pull-down), you can hide all the questions which relate to a Yes answer. Hope this helps. Pete On Aug 26, 12:15 pm, EddS wrote: I have produced a simple questionaire in excel i.e. 20 questions, with a reponse predefined as YES or NO. In another sheet "Report-calcs" it will list the answers for the user. The following basically checks for NO and then if no gives the predefined answer to the question =IF('4 Data Quality'!C7="No",'4 Data Quality'!E7,0) C7 = the response, E7 = a hidden answer to the question In my "report page" I have this formula on around 100 lines Problem with this if the answer was YES it would have an empty space - Question - how to I create a questionaire, with only answers to "NO" being shown in a report (blank or YES elements removed) The report will probably have 5 or 6 sections (i.e. H & S, DDR, DQ) with each section having predefined answers. I thought about Vlookup but not sure how to make it do this. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a Questinaire with filtered responses
Perhaps you could have an event macro which re-applied that filter
whenever there was a change to the responses. However, your users would have to be trained to allow Macros when the file is opened. Pete On Aug 26, 1:12*pm, EddS wrote: Pete - thanks - I have done that but this still requires user intervention to filter the report - this will cause errors with people forgetting what to do. I'll keep trying "Pete_UK" wrote: Instead of returning a zero in your formula, you could return a blank: =IF('4 Data Quality'!C7="No",'4 Data Quality'!E7,"") Then, by applying Autofilter to the column with this formula in (choosing Non-blanks from the fulter pull-down), you can hide all the questions which relate to a Yes answer. Hope this helps. Pete On Aug 26, 12:15 pm, EddS wrote: I have produced a simple questionaire in excel i.e. 20 questions, with a reponse predefined as YES or NO. In another sheet "Report-calcs" it will list the answers for the user.. The following basically checks for NO and then if no gives the predefined answer to the question =IF('4 Data Quality'!C7="No",'4 Data Quality'!E7,0) C7 = the response, E7 = a hidden answer to the question In my "report page" I have this formula on around 100 lines Problem with this if the answer was YES it would have an empty space - Question - how to I create a questionaire, with only answers to "NO" being shown in a report (blank or YES elements removed) The report will probably have 5 or 6 sections (i.e. H & S, DDR, DQ) with each section having predefined answers. I thought about Vlookup but not sure how to make it do this.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Formulas from Filtered Data | Excel Worksheet Functions | |||
no responses... | Excel Discussion (Misc queries) | |||
Creating a numbered list on a filtered worksheet | Excel Worksheet Functions | |||
creating a filtered list | Excel Discussion (Misc queries) | |||
adding up yes, no, n/a responses | Excel Discussion (Misc queries) |