Creating a Questinaire with filtered responses

[EddS]

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.

 

[Pete_UK]

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

 

[EddS]

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]

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