Today's 'Learn Excel from MrExcel' Podcast is focused on some work provided by 'Dave' from Boca Raton Fire Dept. Dave sends us this tip: "Use a Formula to create a Rolling Date Data Validation." The Date Data Validation Dropdown offers 'Now', '15 Minutes from Now', '15 Minutes Ago' and so on. Follow along in Episode #1428 to learn how its done! Thank you Dave!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1428: rolling date data validation.
Well, hey, welcome back to the MrExcel netcast.
I'm Bill Jelen and a shout-out today to the folks down at Boca Fire.
This is from Dave down at Boca Fire.
Dave has been on the podcast before.
Dave sent in a brilliant, brilliant worksheet.
I love this.
Okay, we're interested in the data validation over here in column D.
Now, Dave has to log events, right?
When he gets around to logging an event, it's either something that happened just now or 15 minutes ago or 30 minutes ago or maybe he's on his way and it's going to happen in 15 minutes.
So, he has a very specific range of days he has to enter.
Now, check this out.
You'll notice that there's no data validation.
It’s currently allowing any value.
So, it's really interesting.
When he comes along here and enters the new station, so let’s say station two, because this is a table-- Ctrl T in Excel 2007 or 2010 or Ctrl L, the data validation automatically extends.
So, hey, check it out.
We now have data validation that was just created like kind of on the fly.
Now, I'm recording this at 9:15 coming up on 7:45 a.m.
Check this out.
It is offering a nice range of times that include right now, 15 minutes ago, 30 minutes ago and so on.
I can choose from-- choose from that list.
How is this happening?
Well, it's kind of cool.
I'm going-- I'm going to scroll over here, we have =NOW().
So, that is the current time and then an increment here of 15 minutes.
So, he's just subtracting the current time, that cell minus 15 minus 15 minus 15 minus 15 plus 15 plus 15 and so on.
This data validation, Alt DL, is set up to use a name range called DVL time.
Let's take a look at that.
So, formulas, name manager, DVL time is equal DV TIME NOW, that's the sheet, G2 to G11.
So, it's just hard to go to that range.
We can actually move this range back to hidden worksheet, so we-- no one even knows it's there.
It's always updating and life is good.
The beautiful thing here is once you've entered a value, the data validation doesn't matter.
So, the fact that that data validation is now moving to be something later isn't affecting us.
Provided we never have to go back and change anything, we're good.
The value is there, it's hard-coded, it's in, we don’t have to worry about it.
So, this is a very clever way-- clever way to have a rolling validation and also, the fact that that validation will grow with the table.
So that's kind of a slick little feature there.
I want to send thanks to Dave and all the folks down at Boca Fire.
A great idea.
I'll be sending an Excel master pen out today.
Hey, 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 1428: rolling date data validation.
Well, hey, welcome back to the MrExcel netcast.
I'm Bill Jelen and a shout-out today to the folks down at Boca Fire.
This is from Dave down at Boca Fire.
Dave has been on the podcast before.
Dave sent in a brilliant, brilliant worksheet.
I love this.
Okay, we're interested in the data validation over here in column D.
Now, Dave has to log events, right?
When he gets around to logging an event, it's either something that happened just now or 15 minutes ago or 30 minutes ago or maybe he's on his way and it's going to happen in 15 minutes.
So, he has a very specific range of days he has to enter.
Now, check this out.
You'll notice that there's no data validation.
It’s currently allowing any value.
So, it's really interesting.
When he comes along here and enters the new station, so let’s say station two, because this is a table-- Ctrl T in Excel 2007 or 2010 or Ctrl L, the data validation automatically extends.
So, hey, check it out.
We now have data validation that was just created like kind of on the fly.
Now, I'm recording this at 9:15 coming up on 7:45 a.m.
Check this out.
It is offering a nice range of times that include right now, 15 minutes ago, 30 minutes ago and so on.
I can choose from-- choose from that list.
How is this happening?
Well, it's kind of cool.
I'm going-- I'm going to scroll over here, we have =NOW().
So, that is the current time and then an increment here of 15 minutes.
So, he's just subtracting the current time, that cell minus 15 minus 15 minus 15 minus 15 plus 15 plus 15 and so on.
This data validation, Alt DL, is set up to use a name range called DVL time.
Let's take a look at that.
So, formulas, name manager, DVL time is equal DV TIME NOW, that's the sheet, G2 to G11.
So, it's just hard to go to that range.
We can actually move this range back to hidden worksheet, so we-- no one even knows it's there.
It's always updating and life is good.
The beautiful thing here is once you've entered a value, the data validation doesn't matter.
So, the fact that that data validation is now moving to be something later isn't affecting us.
Provided we never have to go back and change anything, we're good.
The value is there, it's hard-coded, it's in, we don’t have to worry about it.
So, this is a very clever way-- clever way to have a rolling validation and also, the fact that that validation will grow with the table.
So that's kind of a slick little feature there.
I want to send thanks to Dave and all the folks down at Boca Fire.
A great idea.
I'll be sending an Excel master pen out today.
Hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
