1

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

Improve this question
2
  • 1
    I suspect that google spreadsheets might be easier here - it will probably do most of what you need
    – Journeyman Geek ♦
    Mar 11, 2013 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, 2013 at 8:30

1 Answer 1

Reset to default
3

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.

Improve this answer
3
  • 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, 2013 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, 2013 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, 2013 at 6:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .