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?

  • 1
    I suggest creating and bookmarking a filter view for each class.
    – user79865
    May 20 '16 at 2:02
  • For readability sake, here is a rewrite of the formula you wrote: =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.
    – Xzila
    Sep 23 '16 at 18:13

Instead of using drop-downs from data validation and formulas put all the information in a single sheet and use filters or filters views. For details see the official help article Filter your data - Docs editors Help

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy