So im trying to create a simple spreadsheet to keep track of a small inventory of items and i need excel to create a static time stamp. These items have a unique number and the current function already looks up the number in a given range of cells and if it finds it, it places a time stamp but the problem is that every time i open excel it updates all the time stamps. This is my current function: =IF(COUNTIF($N$3:$N$10003,E13),NOW(),"") Ive tried some things that i found on Google but i cant seem to make them work. Can anyone help me by modifying the current function or creating a new one? Thanks in advance
I think the problem may be that NOW statement. What you need to do is a condition that if that number doesn't exist it does what you have but if it does exist it branches else where.
NOW is the only function that works as a time stamp AFAIK. I found that you could create custom functions in VBA in Excel but my knowledge in Excel and any kind of programming is almost non existent to understand it. Still havent found any way to solve my issue. Help!
I don't know that I can be of too much help but the problem with NOW is that NOW is always NOW. Every time it reads NOW it is the time right now. This is going to require something advanced or possibly even an external data source (MS Access, etc.). You may get some help here, but if you know of any forums where they discuss Microsoft Office at an advanced level you may have more luck. Or who knows, maybe someone will make me look stupid and post an easy answer. I hope so. Good luck.
I'm assuming when you add each of the record rows the col with the timestamp is =now() if so replace the way you do this with a macro, make a button and paste special values the cell with the timestamp or make them value before you exit little macro that Copies col c to col d as values Sub makestaticcolD() Set myCell = ActiveSheet.Range("c1") For i = 0 To 18 myCell.Offset(i, 0).Copy myCell.Offset(i, 1).PasteSpecial xlPasteValues Next i End Sub
Ive been trying to do that circular reference workaround shown in the internet but i cant get it to work. In order to make the formula even simpler now i-ve set a column of cells to show if the item unique number is found and show "Yes" and trying to time stamp the other cell with the circular reference workaround but i cant get it to work.
Oh, the joys of volatile functions! See if Solution 2 from https://www.exceltip.com/excel-text...atic-recalculation-of-volatile-functions.html might be of help in your design. If not, describe your data a bit as well as the workflow. There will need to be another column or two to flag whether to keep the old date or put in a new one as the contents of column N change perhaps?
Thanks for the replies guys. After trying all recommendations shown in Google results, i found one that works. What i did was set a column exclusively for showing if the number was found in a range of cells, if it was found then it said yes, then in another cell i used a circular reference function as recommended and it worked. Now the dates no longer auto update.