This spreadsheet is a lesson planner for teachers. It will be used by a wide variety of teachers with varying tech skills so it needs to be straightforward and not easily messed up.

The first sheet in the spreadsheet is meant to be a calendar of sorts that shows the lesson titles and allows them to be rearranged. The lessons are created by a button which runs a script which creates a new tab for the lesson.

Then, there is a sheet called "Sheet Names" which uses a script to create a list of the tabs (lessons) in the spreadsheet. The first sheet is data validated to create a dropdown of the lesson titles taken from "Sheet Names." It then uses a script to replace the data validated text with an actual link to the tab.

The problem is that the links to the tabs are created by a vlookup-style script which finds the row of the corresponding lesson title in "Sheet Names." But if the tabs become reordered, or a lesson (tab) is deleted, the row numbers of "Sheet Names" refer to a different lesson and so the calendar in the first sheet gets messed up.

I need to find a way that:

  1. The lesson calendar on the first sheet should be able to be rearranged fairly easily
  2. Lessons (tabs) should be able to be linked to the corresponding tab
  3. Lessons (tabs) should be able to be deleted without messing up the order of the lesson calendar

Your Answer

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

Browse other questions tagged or ask your own question.