I have a spreadsheet that I have spread out over many sheets. However, I could automate the filling out of some simple data by doing some simple calculations. I could program something simple like this, but I have been looking at Google's function list, and I cannot see anything that would allow me to do this. Everything I need to automate needs access to the current sheet number.
So these are the properties of my spreadsheet...
- I have 7 different classes I am looping through, and each one of them have specific things that need to be done for the months of December, January, and February
Each month is a new spreadsheet (Should be automated - 1 cell)
- Sheet 1 - December ---- NOTE: Sheet 1, 2, and 3 are for 1 Class (ie. DA - December)
- Sheet 2 - January
- Sheet 3 - February
- Sheet 4 - December
- Sheet 5 - January
For each month, things are divided into 2 weeks. (Should be automated - 2 cells)
- Sheet 1 - Week 1, Week 2
- Sheet 2 - Week 3, Week 4
- Sheet 3 - Week 5, Week 6
For each month, there are only 6 items (3 items per week) (Should be automated - 6 cells)
- Sheet 1 - Day 1, Day 2, Day 3, Day 4, Day 5, Day 6
- Sheet 2 - Day 7, Day 8, Day 9, Day 10, Day 11, Day 12
I have been trying to find functions that could allow me to do this, but I don't even know if it is possible. I have seen that there use to be a script gallery, but that is not the add-on manager. I have not seen anything in there to solve my issue
I figured if I could get the current sheet count, then I could easily do this myself. Just apply a little algebra and some though and I could automate this process.
For the months, I can have a formula with modulus - 1, 2, 3, 4, 5, 6, 7, etc, and turn it into ... - 1, 2, 3, 1, 2, 3, 1, etc..
For the weeks, I can have a simple formula like
- = ([current sheet #] * 2)
- = ([current sheet #] * 2) - 1
For the days, I can have a simple formula like
- = ([current sheet #] * 6)
- = ([current sheet #] * 6) - 1
- = ([current sheet #] * 6) - 6