Generating summary sheet from excel form

Hi All!

I am in need of some serious help for a college project. I have created a standard excel form that is to be complete electronically by over 600 people. I would like to generate a summary sheet that collects the critical information from the form. One of the items I need to summarize is the selection choice from a drop down menu. Is there a way to reference the cell to return the selected value (it is not numerical i.e. what department your in). Also as the forms are returned they will be added into a master spreadsheet with the summary page first and then the returned forms in the sheets following. Is there a way I can reference each of the following sheets without having to reformat the formulas for each of the 600 sheets/forms? I know this is complicated and I appreciate ANY help that is provided. Thank you!

Here is what I am working with, also open to any other suggestion on how to improve or simplify if I am going about it the wrong way. Thank you foryour help!






FLEX FORM DRAFT.xlsx

Yes the form cells will remain consistent for each of the forms, so the drop down menu should always be located in G3. The expected output should be Part-time or full-time faculty.

VBA solution attached now
its less volatile and probably more adaptable

though it does rely on your hidden sheets not being hidden (1004 error with worksheet selecting)
as the administrator of the file i assume this is not a problem for you

Summary sheet must be the 4th sheet from left in order (order matters)
anything before 4th is ignored
everything after is added to loop

Thank you for you help! I am going to sound like a completely incompetent but I cannot open your file correctly. When I do it asks about enabling macros, which I do, then it says its unreadable and to "open and repair", then asks me two more times about enabling macros then to save the log book? Once the document is open the values are in the summary sheet but no formulas. Any ideas as to what I am doing wrong? Thank you again!

Thank you all for your help and suggestions! Special thanks to Sixthsense, your solution worked pefectly and without your help I would not have been able to complete my document! Thanks again!