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:
- The lesson calendar on the first sheet should be able to be rearranged fairly easily
- Lessons (tabs) should be able to be linked to the corresponding tab
- Lessons (tabs) should be able to be deleted without messing up the order of the lesson calendar