Automatic/Immediate conversion of formula results to text

[cboyle]

I have a call log in which I enter the following information for every call:
My Name, Date, Time of Call, Caller's ID number, Caller Site location(static).

To expedite the data entry, I wrote a few formulas that autofill this information as soon as I enter the ID number.

For example:

Time of call column:
=IF(ISBLANK(D1), "", TODAY())

As soon as I enter the ID, or anything into the ID cell, the time of call is automatically entered.

I'm running into the problem that every time I reopen the document or save it, all of the values automatically update to the current time of opening/saving. I lose all of the necessary information regarding the time and date of the call, because it all changes to the current date and time. I understand this refreshing these formulas is a standard excel procedure.

Copying the Time and Date and Pasting special as values solves the problem, but is tedious and requires manual effort.

Is there a way to automatically convert a formula result to text/string as soon as the formula computes a result?

Thanks in advance for the help

 

[AlphaFrog]

This will automatically date stamp column B when you make an entry in column D

Install the code in the worksheet's code module:

• Right-click on the sheet tab
• Select View Code from the pop-up context menu
• Paste the code in the VBA editor

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Count = 1 [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] Target.Column = 4 [COLOR=darkblue]Then[/COLOR]  [COLOR=green]'Column D changed[/COLOR]
            [COLOR=darkblue]If[/COLOR] Target <> "" [COLOR=darkblue]Then[/COLOR]
                Target.Offset(, -2).Value = [COLOR=darkblue]Date[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[cboyle]

@AlphaFrog That worked like a dream, many thanks

 

[AlphaFrog]

You're welcome. Thanks for the feedback.