Freeze =Today() formula once data has been entered

[Jyggalag]

Hi all,

I am currently using this formula:

With its neighbor formula in column C going like this:

My question is whether it would be possible to somehow freeze my data in cell B3, C3 etc. going forward? So once the formula detects that a data entry has been in the cell 2 rows below it, it will update the date and then freeze this date.

Is this possible? It would assist me a lot. Thank you!

Kind regards,
Jyggalag

 

[Micron]

Perhaps:
Select those cells, Ctrl+C to copy, Shift+F10 (release), press V to paste as data?

 

[Jyggalag]

Perhaps:
Select those cells, Ctrl+C to copy, Shift+F10 (release), press V to paste as data?

Not entirely sure how this would work. But I probably didn't explain it correctly either.

Essentially I would like to know if there is a way to make the formula work, but only once? So once it has made its first entry, it does not update it, if that makes sense?

Apologies and thank you Micron!

 

[MARK858]

No you can't freeze the formula, you either need to convert it to values as suggested or use VBA (there is a workaround with iterations but I don't recommend it).

The other option other than a formula is to use Ctrl + ;

 

[Jyggalag]

No you can't freeze the formula, you either need to convert it to values as suggested or use VBA (there is a workaround with iterations but I don't recommend it).

The other option other than a formula is to use Ctrl + ;

Ah I thought so,

Do you know of a VBA solution for this?

 

[MARK858]

Yes, there have been plenty posted on the forum so you can either do a search or I'll post something when I get in this evening

 

[Jyggalag]

Yes, there have been plenty posted on the forum so you can either do a search or I'll post something when I get in this evening

Perfect! I will try to search, but nevertheless if you find a good one, pleast post it here, so I can mark it as the solution to this one

Thank you very much Mark858!

 

[Micron]

Essentially I would like to know if there is a way to make the formula work, but only once? So once it has made its first entry, it does not update it, if that makes sense?

Doesn't my answer accomplish that or do you want it to happen auto-magically after the calc is made? For that you'd need to capture the sheet change event or something? I don't see how that would work either if you dragged this formula through a range as the calcs would happen immediately. You seem to be looking for a complicated vba solution that could be accomplished with 5 key presses.

 

[Jyggalag]

Doesn't my answer accomplish that or do you want it to happen auto-magically after the calc is made? For that you'd need to capture the sheet change event or something? I don't see how that would work either if you dragged this formula through a range as the calcs would happen immediately. You seem to be looking for a complicated vba solution that could be accomplished with 5 key presses.

You're correct, I'm probably wishing too hard haha

Maybe a solution could also be to make 5 macros that converts the formulas into raw data for each date? So you open the excel file, fill in your data and watch the dates update. Then you press the macro and it converts the data formula into raw data = current date that wont change going forward. Then you save the excel file ("File for February 2022") and keep the other excel file as a master template with the formulas intact for other months

May seem complex as well, but simplest solution I can think of that has automation in it

 

[Micron]

If Range(someRange) = "" then...

To figure out the macro to replicate the keyboard shortcuts (or any other thing you want to figure out) you can record the sequence as a macro to get how that part is done. I did:

    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Incorporate/modify to suit, as in replace Selection.Copy with something that targets the range if need be, but you still need a way to fire the macro. Not sure if running it from ribbon clicks is all that much better than the keyboard inputs. However, no, 1 macro, not 5.