Timestamp - No VBA

[Newbienew]

Good day Pros,

I was doing some research on a static timestamp as I was trying to apply it to a project I was working on. Is there a way to make a formula static? I have used =IF(A1<>"", IF(B1="", NOW(), B1), "") and I have used a lamda version as well. =lambda(x,y,if(len,x))(now(),b3). In both cases, no matter what I do, all the cells that have the formula in them update with each entry. Please Help. Thank you

 

[6StringJazzer]

What cell is your formula in?

=IF(A1<>"", IF(B1="", NOW(), B1), "")

If it's in B1 it should work if you enable iterative calculations. If not, tell us more about what you want to do.

 

[Newbienew]

I do apologize. I should have mentioned that in my initial posting. I do have that box checked off.

 

[6StringJazzer]

It works for me when the formula is in B1 (the original, not the LAMBDA version).

If you are doing exactly the same thing I'm doing but it doesn't work, then I have no further suggestions.

 

[Newbienew]

I tried it both ways, but for every entry, the time changes and updates. Is this the same for you?

 

[Alex Blakenburg]

Jeff's suggestion was to check the iterative calculation (tun it on) and then use the non-lambda version.
Did you try that combination and did that not work for you ?

Note: I have seen 2 high profile Excel Youtubers use this technique without providing any warnings.
I think turning on iterative calculations is a terrible idea. It is an application level setting which will impact all open workbooks and will continue to do so until you turn it off again. It will turn off all circular reference warnings and if you have a lot of circular references in the open workbooks it is significant performance drain.
Also if the workbook is used by others it is unlikely they will have the check box turned on.

The most reliable way is to use VBA.

 

[6StringJazzer]

make a formula static

What is the reason for avoiding VBA?

 

[Newbienew]

Jeff's suggestion was to check the iterative calculation (tun it on) and then use the non-lambda version.
Did you try that combination and did that not work for you ?

Note: I have seen 2 high profile Excel Youtubers use this technique without providing any warnings.
I think turning on iterative calculations is a terrible idea. It is an application level setting which will impact all open workbooks and will continue to do so until you turn it off again. It will turn off all circular reference warnings and if you have a lot of circular references in the open workbooks it is significant performance drain.
Also if the workbook is used by others it is unlikely they will have the check box turned on.

The most reliable way is to use VBA.

Yes I did. In my second post I mentioned I should of stated I had it checked off the iterative. No it did not work.
Others will be using the workbook

 

[Newbienew]

What is the reason for avoiding VBA?

This has to be uploaded on a TEAMS as it does not support VBA.

 

[MARK858]

If iterative calculation doesn't work for you (I won't use it btw) and you can't use VBA (not sure if Teams supports Office Script, but if it does then you would need to run it each time) then the only other option AFAIK is to manually press

Ctrl-Shift + ;