sheet 1 result - auto fill in to sheet 2 list

Hello everyone,

this are the testing format for my excel project
SHEET 1.png

from the sheet 1, i already key in all the data

but,

SHEET 2.png

how can i link example -
sheet1 a2 (student id), a3 (name), c13 (total mark), and g7,8,9,10,11 (grade) to auto fill in sheet2 according to columns line, but after it fill up to sheet2 the sheet1 will be empty and start for new student id,name,marks and grade

is it possible to do that formula?

Hello Fish
I've put together a form and record sheet for you to look at. It uses VBA code to transfer the form data to the Records sheet. The Form sheet is protected and only the yellow cells accept data. You can overwrite the labels in the ID and Name cells if you wish, as they will be restored when the cells are cleared. There's some data validation in the Marks cells to keep the scores within the mark range and a message box to warn that an ID must be entered as the ID column in sheet 2 keeps the row count for records.

There's also a lookup table to get the Grade based on the Total marks.

Hope this helps.
DBY

title updated