CTRL semicolon
Is there any way to automatically lock in the date after you pull it up with the TODAY function? Or is there another function that will do what I'm trying to do?
I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.
Thanks in advance!
CTRL semicolon
"The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell
Sorry to be dense. At what point to I do CTRL semicolon? Will that do it automatically? I'm trying to figure out how to have it lock in automatically as soon as it pulls up the date.
If you type in CTRL + ; an unchanging date will be entered.
Alternatively type =TODAY()
(don't press Enter)
and press F9.
HTH, Peter
Please test any code on a copy of your workbook.
What does F9 do in this situation?
Here is my formula: =IF(N5="","",TODAY())
This will pull up today's date if there is any value in N5. If N5 is blank, it will remain blank.
What I'm trying to figure out is how to make it freeze the date once somebody puts a value into N5 so that I never have to touch it.
PS, CTRL ; is a great little shortcut. Don't know why that one's never crossed my path before.
This is a tad ragged but should work.....
......I think :oCode:Private Sub Worksheet_SelectionChange(ByVal Target As Range) a = Date If Range("n5").Value <> Blank Then If Range("a1").Value = Blank Then Range("a1").Value = a Exit Sub End If If Range("a1").Value < a Then Exit Sub End If Range("a1").Value = a End If End Sub
Veni, Vidi, Velcro - I came, I saw, I stuck around
Microsoft Office Specialist Master (2013)
Thanks Disco, I'll give it a shot.
BTW, if anybody knows how to do it outside of VBA, it'd be real helpful since I'm still in the learning stages of VBA.
There is a way, fraught with danger. If you enable circular references (Options\Calculation and check "Iteration") you can write formulas that self-reference. Enable CR's, clear the contents of cell N5, and enter the following formula in cell O5:
(I've assumed that column O is where you want the date.) Once this is in cell O5, copy it down as far as you'll need, plus a few rows.Code:=IF(NOT(O5=0),O5,IF(ISBLANK(N5),0,TODAY()))
1. The formula first looks to it's own cell to find out if there's a date there already; if so the formula puts that date back in the cell. (For this reason, if you need to extend this formula down to more rows later, you must copy it from a cell whose result is zero, not a date.)
2. Then the formula looks at cell N5; if it's NOT empty, the formula puts today's date in the cell.
You'll now have zeroes in those cells until you enter something in the corresponding cells in column N. (Anything: text, numbers, formulas, they all trigger the ISBLANK logic in column O.)
The danger is you are now unprotected from ACCIDENTAL circular references in other cells. If that's not a concern, the above should work.
Gardnertoo: I just like to smile! Smiling's my favorite.
Boss: Make work your favorite. That's your new favorite.
Home: Windows 7, Excel 2013
Previous Employer: Windows XP and Windows 7, Excel 2003 and 2007 and 2010
Current Employer: Google Sheets
Hi, Dan.
There is no way to conditionally enter a static date without using VBA.
A couple of follow-up questions:
(1) What should happen if someone *changes* N5? The date cell change to today's date (i.e. show the date of the last update)? Or, do you just want the date cell to show the *first* time N5 was updated?
(2) Also, what if someone deletes the value in the date cell? (or do you want to prevent that from happening)?
"The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell
Like this thread? Share it with others