I teach a middle school robotics class and we do an inventory check every six weeks to make sure that all the pieces are there. I would like to track over different class periods how the inventory is going. I would like to write a script that would allow me to click a button to lock down all columns across all sheets except for the column that I want students to edit.

In other words, during 1st period the only column that students could edit is column G. Then I click a button/icon at the end of class and that column is locked.

I then click another button/icon and only 2nd period (column H) is open. I then click it to lock down again.

I know that I could do this by clicking protect cells but I am trying to find a more efficient way.

Here is the spreadsheet:


1 Answer 1


May I suggest that you are over-complicating things.

  • You have a spreadsheet that will serve as a template.
  • In each of the "bucket" sheets,
    • delete contents of "G1:M1", "G23:M23", "G32:M32", "G41:M41", "G67:M67", "G76:M76", "G85:M85", "G98:M98", "G107:G107"
    • Cell G1, G23,G32,G41,G67,G76,G95,G98,G107 = "Actual Counted"
  • Each time you want to do a stocktake,
    • make a copy of the "template'
    • name the new spreadsheets for the "period" group AND the date. For example, "Period 1_ 202201112_22-23 Spike Inventory Stocktake"
    • when these files are saved, they will sort by "period" and then by date.
  • Conditional formatting
    • if you wish, you could use conditional formatting to highlight cells to stocktake, and some results such as "=expected qty","<>expected qty", and "negative"

Sample conditional formatting


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.