So, here's my issue. I've got three sheets. One sheet has all of the classes for Mondays and Wednesdays, one sheet has all the classes for Tuesdays and Thursdays. The third sheet has a drop-down menu in cell A1 with a list of all of the classes. By putting the in the following formula, I was able to make it so that the "Student Name" column populates with the corresponding information from the correct sheet:
=IF(A1="Character Education", ARRAYFORMULA('Mon/Wed'!A:A), IF(A1="Biology", ARRAYFORMULA('Mon/Wed'!D:D), IF(A1="English A", ARRAYFORMULA('Mon/Wed'!G:G), IF(A1="English B", ARRAYFORMULA('Mon/Wed'!J:J), IF(A1="English C", ARRAYFORMULA('Mon/Wed'!M:M), If(A1="History", ARRAYFORMULA('Tu/Th'!A:A), IF(A1="Math A", ARRAYFORMULA('Tu/Th'!D:D), IF(A1="Math B", ARRAYFORMULA('Tu/Th'!G:G), If(A1="Math C", ARRAYFORMULA('Tu/Th'!J:J), IF(A1="Spanish", ARRAYFORMULA('Tu/Th'!M:M)))))))))))
However, while it does populate the column accordingly, it does not make it so that you can edit the data as well.
My goal: Make it so that instead of scrolling through the sheets to find the class, you can just click the drop down, have only that class display, and then be able to edit the information of that class. Alternative solution: hiding/displaying groups of columns based on drop-down value. Make sense?
=ArrayFormula(CHOOSE(MATCH(A1,{"Character Education","Biology","English A","English B","English C","History","Math A","Math B","Math C","Spanish"},0),'Mon/Wed'!A:A,'Mon/Wed'!D:D,'Mon/Wed'!G:G,'Mon/Wed'!J:J,'Mon/Wed'!M:M,'Tu/Th'!A:A,'Tu/Th'!D:D,'Tu/Th'!G:G,'Tu/Th'!J:J,'Tu/Th'!M:M))This one uses an explicit array of the possible content of cell A1 in your 3rd sheet, and uses match formula on that 1d array to generate a number 1 through 10 which is used in the choose formula to tell it which range to display.