Can I produce text-based reports using Excel dropdown lists?

[dcuk89]

I'm a teacher, and I really enjoy finding efficient solutions for some of the tasks that my job requires.
I had an idea about how I might be able to efficiently produce student reports, and need some help in working out how to do it. I'm relatively new to Excel, but am keen to learn.

I'm looking to create a kind of program that will consist of the students names, and a number of headers (Attendance, Punctuality, Attitude, Achievement, etc.). For each header, there will be between 5-7 options that I would like to select from a (ranging from worst to best, on a dropdown list).

I would like to be able to select an option from each dropdown for the student, and at the end produce a summary (whereby the text from each dropdown will be collated together). Also, I was hoping to replace each instance of 'X' with the students name.

Is this possible to achieve?
Any help is HUGELY appreciated!

 

[My Aswer Is This]

Yes Excel can do many things.
The best way in my opinion is to set up Excel with this in mind.

Each row in Excel should be considered as a Record and each column considered as a field.

A record is like a thing Like Person a Car or a House
A field is like a particular thing about the record like with Person it would be:
Name, Address, Age, Grade etc

So in Row (1) we put all the fields. Like Name, Age, Grade
Then stating in row two we enter our data so row (2) would look like

George in column "A"
12 in column "B"
6 in column "C"

Then later on we can write Vba scripts to manipulate this data in many different ways.

Hope I did not explain things you already knew.

 

[dcuk89]

Yes Excel can do many things.
The best way in my opinion is to set up Excel with this in mind.

Each row in Excel should be considered as a Record and each column considered as a field.

A record is like a thing Like Person a Car or a House
A field is like a particular thing about the record like with Person it would be:
Name, Address, Age, Grade etc

So in Row (1) we put all the fields. Like Name, Age, Grade
Then stating in row two we enter our data so row (2) would look like

George in column "A"
12 in column "B"
6 in column "C"

Then later on we can write Vba scripts to manipulate this data in many different ways.

Hope I did not explain things you already knew.

Thanks so much for your response, that was very helpful.

I have made a start. Here's my spreadsheet so far - https://docs.google.com/spreadsheets/d/1-c7_eRdmYjqy4WFQwd2PXuv1sSl32zAfrWUV9NmiKY0/edit?usp=sharing

It basically does everything I need it to do, except for one function.

The spreadsheet pulls through a list of options and allows the user to select from each list under each header (ie they can select an option for Attendance, Punctuality, etc.).
All of the selections are collated into a 'Report' in the final column. The student's 'common name' is pulled from column B, and replaces 'Xx' within the report.

The only thing I am yet to find a solution for is the replacement of 'He/she' with the correct pronoun, found in column C.

To collate the reports and insert the students' name I use the following formula:
"=SUBSTITUTE(D2,"Xx",B2) & "

" & SUBSTITUTE(E2,"Xx",B2) & "

" & SUBSTITUTE(F2,"Xx",B2) & "

" & SUBSTITUTE(G2,"Xx",B2) & "

" & SUBSTITUTE(H2,"Xx",B2) & "

" & SUBSTITUTE(I2,"Xx",B2)"

How would I go about swapping out 'He/She' with the correct pronoun? What would I need to do to this formula?

Thanks again for any help!