Kevin wants to clear out any data from yesterday's shift Worksheet any time someone opens the Workbook. Using the formula from Episode #1452, plus four tiny macros added today, Bill shows us the solution.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1453: clear for a new day.
Hey, this is a great question sent in by Kevin.
Kevin inherited the spreadsheet.
It has three different worksheets: day shift, swing shift, and graveyard shift.
It only gets used occasionally; a few days a week.
When it gets used, if they enter data, that data has to stay throughout the rest of the shift, but if they open it up on another day, we want to clear out any data from before.
Okay, so here's what we want to do.
We want to insert a brand new worksheet called log.
On this worksheet, we set up the three works-- three shifts: days, swings, and graves.
When the new day starts-- this is just a simple NOW formula and this horrendous formula from yesterday, episode 1452, that figures out if we crossed a new day threshold.
People say me-- say to me, hey, why do you call out an episode?
It's easy to find the video.
Go to YouTube, search for learn Excel and the episode number and it’ll come right up.
Go to Google, put in learn Excel 1452, the first six entries will be that video.
So, if you need to see how this is set up, watch yesterday's podcast.
Two challenges we have.
First of all, we want to log the last time that something changed.
Now, here's what we're going to do.
Back here on the days work sheet, I'm going to press Alt F11.
Over here in the Project Explorer-- if you don't see the Project Explorer, do view project explorer, look for the worksheet-- the workbook that you have.
It might be minimized like that; expand it.
Microsoft Excel objects might be minimized; expand it.
Here's days.
Right click on days and say view code.
Okay, we now have a code pane for just days.
In the left drop-down, choose worksheet and it give us this macro called worksheet selection change.
That's not what we want.
Go ahead and wipe that out.
On the right-hand side-- let me see if I can make this smaller.
On the right-hand side, the other drop-down, you want to go in here.
So, choose worksheet, on the right-hand side, choose change, worksheet change.
This is a tiny little macro that's going to run every time that someone changes something on the days worksheet.
So, we're going to say worksheets log dot range B2 dot value equals now.
In other words, every time that someone makes a change on the days worksheet, I want to go to cell B2 back on the log sheet and write that-- the current date and time.
I did the same thing for swings.
I'm going to double-click and the same thing for graves but it's going to a different cell each time, right?
So, that's just a little bit of work that's going to silently log when-- every-- when any change was made and no one's going to be able to even tell if that's happening.
So, here I am on the days worksheet and I’ll just put in some data here.
Then if we go back to the log worksheet, we should see-- yes, that the change October 13th -- I’m recording ahead of time at 7:08 p.m., the change happened.
If I would go to the swings worksheet and just log some data there, 2 3 and come back to the log, you see that that now updates.
So, that's just happening.
No one sees it happen.
The screen doesn't flash.
You can even hide the log worksheet and no one knows it's there.
Okay, so, now that we have this little bit of information back here, we need to-- a workbook open macro.
So, I'm going to go back to Alt F11 to get the workbook.
Open macro, again, we come here to this workbook and we want to right click and view code or double-click.
Double-click.
Now, I already wrote this ahead of time because I knew you would be running along.
So, I'm going to copy this code and paste it in here.
So, workbook open, go look at the log sheet, go from rows 2 to 4.
If that great formula from yesterday out in column 5, out in column E is equal to true, then I'm going to say that everything from range A4 resize 100 rows comma 10 columns-- you would have to figure out what exactly the right size is-- to clear and it will go and do that.
So, every time that we open this workbook up, if it's a new day, it's going to clear that data out.
All right, so let's actually test this to see how this is working.
So, I'm going to save 1453.
It should clear out the graveyard sheet when I open it up again.
So, we'll file, close, recent, 1453, enable content and the graveyard shift is now cleared out where it had data.
Data is still here, data is still here.
When I come back in tomorrow, those will both get cleared out.
So, just-- actually four macros when you get right down to it.
Tiny little macros on each sheet to log the last time and then the workbook open.
I’ll have to post this in the blogs.
That way you can download a copy if you want to try it yourself.
Okay, well, hey, I want to thank Kevin for sending that question in.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1453: clear for a new day.
Hey, this is a great question sent in by Kevin.
Kevin inherited the spreadsheet.
It has three different worksheets: day shift, swing shift, and graveyard shift.
It only gets used occasionally; a few days a week.
When it gets used, if they enter data, that data has to stay throughout the rest of the shift, but if they open it up on another day, we want to clear out any data from before.
Okay, so here's what we want to do.
We want to insert a brand new worksheet called log.
On this worksheet, we set up the three works-- three shifts: days, swings, and graves.
When the new day starts-- this is just a simple NOW formula and this horrendous formula from yesterday, episode 1452, that figures out if we crossed a new day threshold.
People say me-- say to me, hey, why do you call out an episode?
It's easy to find the video.
Go to YouTube, search for learn Excel and the episode number and it’ll come right up.
Go to Google, put in learn Excel 1452, the first six entries will be that video.
So, if you need to see how this is set up, watch yesterday's podcast.
Two challenges we have.
First of all, we want to log the last time that something changed.
Now, here's what we're going to do.
Back here on the days work sheet, I'm going to press Alt F11.
Over here in the Project Explorer-- if you don't see the Project Explorer, do view project explorer, look for the worksheet-- the workbook that you have.
It might be minimized like that; expand it.
Microsoft Excel objects might be minimized; expand it.
Here's days.
Right click on days and say view code.
Okay, we now have a code pane for just days.
In the left drop-down, choose worksheet and it give us this macro called worksheet selection change.
That's not what we want.
Go ahead and wipe that out.
On the right-hand side-- let me see if I can make this smaller.
On the right-hand side, the other drop-down, you want to go in here.
So, choose worksheet, on the right-hand side, choose change, worksheet change.
This is a tiny little macro that's going to run every time that someone changes something on the days worksheet.
So, we're going to say worksheets log dot range B2 dot value equals now.
In other words, every time that someone makes a change on the days worksheet, I want to go to cell B2 back on the log sheet and write that-- the current date and time.
I did the same thing for swings.
I'm going to double-click and the same thing for graves but it's going to a different cell each time, right?
So, that's just a little bit of work that's going to silently log when-- every-- when any change was made and no one's going to be able to even tell if that's happening.
So, here I am on the days worksheet and I’ll just put in some data here.
Then if we go back to the log worksheet, we should see-- yes, that the change October 13th -- I’m recording ahead of time at 7:08 p.m., the change happened.
If I would go to the swings worksheet and just log some data there, 2 3 and come back to the log, you see that that now updates.
So, that's just happening.
No one sees it happen.
The screen doesn't flash.
You can even hide the log worksheet and no one knows it's there.
Okay, so, now that we have this little bit of information back here, we need to-- a workbook open macro.
So, I'm going to go back to Alt F11 to get the workbook.
Open macro, again, we come here to this workbook and we want to right click and view code or double-click.
Double-click.
Now, I already wrote this ahead of time because I knew you would be running along.
So, I'm going to copy this code and paste it in here.
So, workbook open, go look at the log sheet, go from rows 2 to 4.
If that great formula from yesterday out in column 5, out in column E is equal to true, then I'm going to say that everything from range A4 resize 100 rows comma 10 columns-- you would have to figure out what exactly the right size is-- to clear and it will go and do that.
So, every time that we open this workbook up, if it's a new day, it's going to clear that data out.
All right, so let's actually test this to see how this is working.
So, I'm going to save 1453.
It should clear out the graveyard sheet when I open it up again.
So, we'll file, close, recent, 1453, enable content and the graveyard shift is now cleared out where it had data.
Data is still here, data is still here.
When I come back in tomorrow, those will both get cleared out.
So, just-- actually four macros when you get right down to it.
Tiny little macros on each sheet to log the last time and then the workbook open.
I’ll have to post this in the blogs.
That way you can download a copy if you want to try it yourself.
Okay, well, hey, I want to thank Kevin for sending that question in.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
