I'm currently working on a Google Sheet for work, in which I'm trying to work out whether each student has done a particular paper or not. I want to have two sheets. One sheet will have the students papers in the order they did them, and what their score was. The second sheet will just say which papers a student has done or not done. Here is a mockup: https://docs.google.com/spreadsheets/d/1uADhjE6DSC-Cg_Xqrm8_hMjls3MwvLR8NdzECIQCAAo/edit?usp=sharing
What I want is for the Ys and Ns on the second sheet to be auto-generated. Because the student names won't always be in the same order on both sheets, need something dynamic. I've tried with INDEX MATCH, but can't make it work.
Effectively, I need a formula for the 'Which Papers Done' sheet. It would look at the name, e.g. Abigail, then find the row containing Abigail in 'Papers in Order Taken', and then look in that row for 'Paper 1', 'Paper 2', or 'Paper 3'. If it said 'Paper 1' anywhere in Abigail's row, then it would say Y (or "Yes") in Abigail's 'Paper 1' Column.
Sorry if that's not particularly clear – first time posting a question on here!