Help me separate each student into separate rows without inputting the parent data each time [duplicate]

Question

I am collected data about parents and students. I need there to be a separate row for each student without entering the family data each time. I have always used the "pre-filled link" option to do this but need a less tedious way and want to use a formula to do this on my response sheet. I have found some resources online but I can not get any of the examples to work for me. Here is a copy of the response. I would like each row to include data from A-L (header data?) and make a new row for each student M-T, U-AB, AC-AJ, AK-AR. This is an example but the actual sheet will have more columns. This has 2 tabs, how I my responses are coming out and the second tab is how I want it to look. Any help is greatly appreciated. https://docs.google.com/spreadsheets/d/1QlAk7qXLU5SZJSOHVY4QMCHjeFYdK1-4GrvDjpBMVmk/edit?usp=sharing

Answer 1

Easy. Name a sheet FAMILY Family has columns for

• Code -- a unique string for each family.
• Surname
• Mom's Name
• Dad's name
• Mom's address
• Dad's address if different.
• Phone number
• whatever else is important.

Now on your student sheet:

• Student name
• Other relevant student info
• Family code
• Series of vlookup calls to look up family code on the family page, and return desired info.

The downside of 'every row is a record' is that the rows get unwieldly long.

You can drop down the rabbit hole doing this: In theory you enter data once. That saves a bit of time, but it saves a bunch of time when the data changes.

Families have as a model one set of parents one address and a flock of Little People. Having parents on one sheet and students on another makes this fairly straight forward.

The model sucks.

Couples break up. Now you need two links per student -- one for dad, one for mom. Each with different addresses/phone/secondary contact...

Worse. Sometimes you have additional caregivers Dad is in Europe this year, mom just got committed to the local psychiatric ward, and so the grand parents have custody. Or there is a social worker involved.

At this point you are stretching the spreadsheet model to hell and gone. You really need a relational database with one to many relations possible. MS access does this. So does Ninox if you are on a Mac.