Guidelines for developing an Excel grading sheet

I'm trying to develop a convenient solution for preparation of a summary sheet comprising of lots of grading sheet underneath it.

I have a tabular excel sheet used for obtaining feedback from random users. It contains 20 questions, against which there are four columns: Excellent, Good, Fair, N/a.

In my workbook I currently have 28 feedback sheets. I need to prepare a summary sheet in the exact same formatting. This summary sheet will show percentages of the feedback received. For example: For question 1, 60% excellent, 30% good, 10% fair, and then for all twenty questions.

This I'm doing by manually selecting the cells in all the sheets and dividing them by the number of sheets to get the percentage, and then dragging the formula down.

What I am missing is that sometimes I need to view percentages of some selected feedbacks. For that I am currently changing formulas everytime I need to change the view.

I was thinking is there some way I could develop a Selection sheet, in which all my feedback sheets are listed. I put some tick mark in front of the feedback sheets and only those sheets are taken by the formula. Something like the pivot table, but spread over various sheets.

Any guidelines would be appreciated.

UPDATE: Sample file: Sample file

Faraz

-
I suspect that google spreadsheets might be easier here - it will probably do most of what you need – Journeyman Geek Mar 11 '13 at 11:49
I tried google drive "Forms". I think going forward it can be a good solution because it would require rebuilding of my current feedback forms, so its a good solution, thanks. But currently I've to come up with a solution to perform analysis on current feedback forms. – Faraz Mar 12 '13 at 8:30

Let the golden rule apply:

• Data in one sheet
• reporting in other sheets

It seems that your data collection happens in different sheets. Put all your raw data into ONE data sheet. You may need to add a column to enter the qualifier that is currently represented by the sheet tab. Once you have one sheet with all the data sitting in one contiguous data table, you can create reports with just a few clicks.

Create a Pivot Table to summarize (sum, count, average, group) your data. Add filters to concentrate on aspects of the data.

If need be, create several reports (Pivot Tables), but base them off the same source data.

-
I've added a sample file to the thread. Please have a look. The reason why I have it in separate sheets is that sheet is emailed to everyone and the response is attached with this workbook when it is received. – Faraz Mar 11 '13 at 11:30
It will be extremely hard to get one-click filtering/reporting from that data layout. The data collection happens in a report layout, merged cells, blank row and columns are for visual presentation but hinder analysis. Consolidate all sheets into one data table with columns like company, dept, name, question_number, answer. Then you can build pivot reports easily. If the layout of the sheets is always the same, the consolidation into one data table can be done with VBA. – teylyn Mar 11 '13 at 21:01
I agree. I suppose this would be the best way. You have explained well the golden rule that data in one sheet would be very helpful. Thanks. – Faraz Mar 12 '13 at 6:09