My knowledge of Excel is very limited, and I'm hoping for some guidance as to the best approach I need to tackle the challenge I outline below…
I have an Excel file that is full of data from a SharePoint survey that is used to get feedback from various classes. Some of the questions are ranking questions (likert scale), while others are open-ended 'essay' type questions. This survey is used regularly, so the data is constantly 'growing' as new rows are continually added.
I need to find a way to pull reports from this data, displaying the answers that were received for the questions and the ability to filter only showing data for specific classes, as well as by date, and teacher.
Since the data will continually be exported out of SharePoint into the Excel table, I realize that eventually I will need to have a separate Excel file that reads in the data from the export…but taking things one step at a time, I thought it might be best to just figure how to pull the report content in general just on a different sheet. The link to the sheet below is just an example of the type of data (dummy responses in there now—the real file has 100's of rows, and many more question columns), and on the 'Report' sheet I have demonstrated a general setup of what I eventually want to accomplish (note that I haven't actually set anything up with formulas, etc. yet). Essentially I want to easily be able to see the number of responses for each question (note that in the Data sheet 5=Strongly Agree, 4=Agree, 3=Neutral, etc.). The 'average' column would just show an average numeric value.
I'm able to accomplish that easily for a specific set of rows of data, but I don't know how to set it up so that it can be filtered by any combination of class/date/teacher. I don't know how to do it so that it doesn't have a defined range of rows…it has to be able to work even when new rows of data get added.
The other challenge is including the 'open-ended' answer responses..and how to visually display that. For now I just show an example of having each response in its own column, but I'm open to suggestions!
I know this was a lot of information…but any suggestions/guidance/examples that could be provided would be GREATLY appreciated. I tried to create a Pivot table, but it wouldn't show the info that I needed it to (wasn't showing the list of questions, only a ton of 'collapsible' numbers)
Here is the link to the example spreadsheet: https://www.dropbox.com/s/5nle8zltdvsvasc/surveyExample.xlsx
Thank you!
I have an Excel file that is full of data from a SharePoint survey that is used to get feedback from various classes. Some of the questions are ranking questions (likert scale), while others are open-ended 'essay' type questions. This survey is used regularly, so the data is constantly 'growing' as new rows are continually added.
I need to find a way to pull reports from this data, displaying the answers that were received for the questions and the ability to filter only showing data for specific classes, as well as by date, and teacher.
Since the data will continually be exported out of SharePoint into the Excel table, I realize that eventually I will need to have a separate Excel file that reads in the data from the export…but taking things one step at a time, I thought it might be best to just figure how to pull the report content in general just on a different sheet. The link to the sheet below is just an example of the type of data (dummy responses in there now—the real file has 100's of rows, and many more question columns), and on the 'Report' sheet I have demonstrated a general setup of what I eventually want to accomplish (note that I haven't actually set anything up with formulas, etc. yet). Essentially I want to easily be able to see the number of responses for each question (note that in the Data sheet 5=Strongly Agree, 4=Agree, 3=Neutral, etc.). The 'average' column would just show an average numeric value.
I'm able to accomplish that easily for a specific set of rows of data, but I don't know how to set it up so that it can be filtered by any combination of class/date/teacher. I don't know how to do it so that it doesn't have a defined range of rows…it has to be able to work even when new rows of data get added.
The other challenge is including the 'open-ended' answer responses..and how to visually display that. For now I just show an example of having each response in its own column, but I'm open to suggestions!
I know this was a lot of information…but any suggestions/guidance/examples that could be provided would be GREATLY appreciated. I tried to create a Pivot table, but it wouldn't show the info that I needed it to (wasn't showing the list of questions, only a ton of 'collapsible' numbers)
Here is the link to the example spreadsheet: https://www.dropbox.com/s/5nle8zltdvsvasc/surveyExample.xlsx
Thank you!
