Excel Formula Help!!

[Matt R]

Trying to make a spreadsheet at the moment to monitor incoming calls and time/date recieved.

Column A = Date/Time

Column B = Telephone Number

What I would like is when you type the phone number in Column B it puts the Date/Time in Column A next to it??

Anyone Help??

[scooby_simon]

[ QUOTE ]

Trying to make a spreadsheet at the moment to monitor incoming calls and time/date recieved.

Column A = Date/Time

Column B = Telephone Number

What I would like is when you type the phone number in Column B it puts the Date/Time in Column A next to it??

Anyone Help??

[/ QUOTE ]

not sure what you are asking here, but I assume you have a list of phone and date/time in another sheet and you want to query it thus... "ive a phone no, when did it get called (or called us)"

If so,

Use something like Vlookup.

[Matt R]

I want to use it for incoming call logging, but would like the spreadsheet to automatically put the date/time in Column A each time you enter the next incoming phone number!

[scooby_simon]

Ahh....

use the =now()

so use the formula (in col b) something like =if(A1>1, now(),"") Because the now fuction is within the "if" it may return a Deciamal date time value, in this case just convert it via the standard date/time functions. Problem is that =now() is recalculated each time the spreadsheet is changed.

There must be a way to do this, try google.

[D1MAC]

Getting the exact time in column B is the easy bit use an IF formula so if column A isn't blank the current date/time is returned (which is another formula - NOW or something similar to under date/time functions - I'm trying to jog my memory via OpenOffice Calc).

So, something like =IF(A1<>"",NOW(),"") and then feed down column B, then formatting column B if necessary.

The bit I'm not sure on is how best to lock it so that it doesn't update itself once a value has been entered.

I know you can achieve this via cell/sheet protection but whether or not that's too mechanical (having to protect new cells every time you add a value) and whether or not it can be sorted via a macro I'm not sure/is up to you.

Beaten to it!!!

[Adam]

as said all the above will just be wiped with current date when you re-open file

why not just create a list of now() formulas and when the number is entered, copy paste values over the now() formula on that corresponding row?

[Matt R]

This works:

Dim Cell As Range

For Each Cell In Target

With Cell

If .Column = Range("B:B").Column Then

Cells(.Row, "A").Value = (Now)

End If

End With

Next Cell

[Gaunty]

Create a macro, the only command you need is:

ActiveCell.Value = Now()

You can then call the macro with a Ctrl+letter if you set it.