Auto replace sheet x with y if z?

Question

&nbsp;Im creating a rota for work and am trying to get the sheet to reset at the end off the week to specific values.My thought atm is that when todays date=the beginning of the week date+7 then the sheet reverts back to its default, or copies and pasts a clean one onto it from a locked sheet.Thoughts?Many Thanks

mathetes · Answer

C4rloss&nbsp;&nbsp;Why not just create a Template and use it anew each week? That seems to be the effect of what you're describing anyway.&nbsp;To create a Template, just create that clean sheet you talk about and do a Save As Template&nbsp;

c4rloss · Answer

Trouble is I wont be the one using it. I'll be locking all cells apart from a couple that are restricted to a drop down list, and if they have to do more than one thing other than open the spreadsheet and change times and names then I'll come into work and the computer would have spontaneously combusted XD they cant be given too much freedom.

The plan is to have it all nearly automated so they open one file and every x number of weeks a sheet is wiped clean and they can start again.

I have equations dependant on the names of the sheets so cant delete the entire sheet and create a new one, has to have the existing ones re-set to default.

Hope this makes sense.

mathetes · Answer

C4rloss&nbsp;A template doesn't have to be totally blank. It can be whatever you're describing as the default state--you describe it as "every x number of weeks a sheet is wiped clean and they can start again"--that one with which "they can start again": THAT is your template. All they'd need to do is open the&nbsp;template up and start using it,&nbsp;Does THAT make sense?

c4rloss · Answer

That does and it might have to be a temporary work around, but I don't think it has the final desired outcome unless I'm missing something. Sorry for my terrible description, if having to open and save a new file each time from the template each week then that probably wont work long term, I have seen their file management and there's a good chance someone will delete the wrong things or will end up with several of the same week or files months old, this is what I'm trying to avoid.Many Thanks

mathetes · Answer

C4rloss&nbsp;This was your very first post, with emphasis added:Im creating a rota for work and am trying to get the sheet to reset at the end off the week to specific values.My thought atm is that when todays date=the beginning of the week date+7 then the sheet reverts back to its default, or copies and pasts a clean one onto it from a locked sheet.&nbsp;And the template could/should be something that perfectly satisfies that emphasized part. It is the sheet into which the new week's starting date can be entered--presumably your Excel novices could handle that, and you could use data validation to ensure that it's in Excel date format--and from there whatever defaults are present will also be present.&nbsp;Your most recent concern:it might have to be a temporary work around, but I don't think it has the final desired outcome unless I'm missing something. Sorry for my terrible description, if having to open and save a new file each time from the template each week then that probably wont work long term&nbsp;I think you may be missing something (or there's something I'm missing from your description of the work situation): you don't need to open the template each time you open the file. Once opened at the start of the week, you/they&nbsp;would save it using whatever convention you are using for naming it==e.g.,, "Rotation_7_29_22.xlsx"--and thereafter, for the&nbsp;balance of that week, they use that file.&nbsp;OR, if there's no need to keep archives of past weeks, "CurrentRotation.xlsx." &nbsp;In fact, once a template has been opened and entries made, it prompts you to save as an xlsx file anyway. People would need to learn to start with week (or whatever period you're following) with a fresh file, but beyond that current practices would apply.&nbsp;&nbsp;&nbsp;

Forum Discussion

Auto replace sheet x with y if z?

5 Replies

Resources