I have a spreadsheet that I would like column C2 to timestamp/record date when cell B2 is initialed by a staff member once they have viewed text in cell A2. I used a NOW formula but it updates when I open the spreadsheet the following day. How can I prevent the cell from updating each time I open the document?
My current formula in column c is:
=if(B2<>"",NOW(),"")
Your help is appreciated!
This thread should help.
http://www.excelforum.com/excel-gene...fter-edit.html
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
The best way is a VBA subroutine,
HOWEVER, you can accomplish this within the spreadsheet,
First, goto the Office Icon, upper left corner of the spreadsheet window, click on Excel Options,Formulas,make the checkbox option of Enable iterativecalculation, set Maximum Iterations to 1
now assuming C2 is where the formula is
Use the developer tab to create a check box in D2 (cell link it to D2), and some sort of directions so that when a persons updates B2, they click the box TWICE (the first one lets the following formula(s) do what they need, the second cuts off further calculations based on the cell)
In C2
=IF(AND(B2<>"",D2=TRUE),NOW(),C2)
Hope this helps
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Thank you both. I have added the VBA code and it worked great for my need so far. I will check back in tomorrow for another test to see how it will work when I update my data.
I also created the checkboxes, but it kept giving me the "TRUE" or "FALSE" when I checked/unchecked the box instead of the date. Not sure what I am doing wrong.
Thanks again!
I think you probably used them wrong, or misinterpreted what I wrote, the checkbox is only a way to determine whether or not the date stamp should be re-calculated, there should be no other formula in that cell, the calculations afterward use that cell to determine whether to use the existing cell data, or calculate new cell data, the reason you need to click it TWICE when you want a new date is so the following formulas re-calculated, then stop until the next double click,
Also, I did say that the best way was VBA, this was just an alternative to it
I definitely used them incorrectly. Your instruction was perfect, I just confused myself. Not as adept at Excel as I wish to be...yet.
no problem
Looks like AlphaFrog got you onto something that worked for you, so all is good
AlphaFrog pointed me in the right direction and this is what I ended up with, which suits my need.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
Range("C" & Target.Row) = Now
End If
If Not Intersect(Target, Columns("E:E")) Is Nothing Then
Range("F" & Target.Row) = Now
End If
End Sub
However, let's say a staff member accidently deletes their initials, it resets the date. Can I add something that will lock the column after entry so that others cannot accidently delete it?
A little more background in case you need it: Column A will constantly have communication added that will need to be viewed by multiple staff members. Each staff has their own column...
Again, your help is greatly appreciated!
Try this. Once a time stamp is in column C or F, it wont change
Unlock all the cells that you want the user to Change
Lock the cells in columns C and F
Please Login or Register to view this content.
Last edited by AlphaFrog; 02-10-2014 at 12:15 AM.
Unfortunately, I am still doing something wrong. I got a compile error when I tried it. I will attached the file so maybe you can see what I am doing wrong. Thank you again for your time.
I want it the cell in which the staff initials to lock because I noticed that when I delete it and try it put it back (say someone else does it by mistake and try's to correct it) it gives todays date - which is what it should do...right? Hope this makes sense...
Also, I have a prompt at the beginning of my file because most of staff does not know how to use macros...
test comm.xlsm
You can only have one Worksheet_Change procedure. You have two.
This should replace all your other Worksheet_Change procedures.
Please Login or Register to view this content.
Jumping up and down with joy! By George I think you've solved it! It's working great in my test file. I will implement it into my working file tomorrow at work and see how it goes. Thank you ever so much for all of your help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks