[ms office - 2010] Data form in Excel

[MisterP]

On the first day back a member of staff has asked,

 

is it possible to to use my data collection form in Excel to do some data analysis....

 

looking at the form (see image) they have cleverly distilled all the information in to a standard form with different sections, my first thought is to create separate data sheet and consolidate but there must be a way to read the forms and enter the different cells into a database - thoughts / ideas?

 

 

Happy New Year

[psumner1992]

It depends which parts you want to analyse.

 

I could be reading your request wrong but I will try and help.

 

I would have an extra sheet within the spreadsheet and perform some countif formulas on this data to give you some summaries, you can then convert those into percentages and into graphs if necessary.

 

For example to count how many are on track etc or how many have achieved a 9-5 grade.

 

Hope that helps.

[MisterP]

Thanks for the response , but each pupil will have a sheet and I need an effective way of collating them into one sheet.... pupils will be added as well. My plan is to create a second sheet in the work book and flat file the data so that I could use the consolidate function in Excel to put all of them into one sheet...

 

Sorry not the best description of the problem.

[psumner1992]

That’s more complicated than I thought.

 

Sorry I won’t be able to help.

[MisterP]

Tell me about it....

[Bedders]

From the looks of it you'd need VBA macros and/or custom formulae to detect the number of sheets, get the names of them all, write that out to an array, then you have your list of students.

Next get each set of data using a loop and write it out into the one sheet.

Now you can summarise from your flat-file sheet into graphs/percentages/whatever you need.

 

For a project like this I would need them to define a specific goal. What do they want the spreadsheet to actually do, in simple terms? Get them to explain the exact analysis they need, and why they need it - if they just say 'Oh, well, I want to view all students' progress in my class' then that's not specific enough for you to work with.

 

Good luck! If you need any help give a shout.