HI,

This is probably simple but i'm having a tough time....
I am creating a note sheet for clients....if a client posts a note, I want
the cell next to it to display the date and time it was posted. I know I need
an if formula with istext and now in it but I can't seem to make it
work....Help!

Thanks
Cassandra 112

Hi, Cassandra-
This formula worked:
=IF(AND(C5<"",ISTEXT(C5)),NOW(),"")
It checks to see that the cell in question is both text and non-blank
and returns the time, or a blank if that condition is not true.

HOWEVER... the problem I see is the NOW() function evaluates
differently as time passes. If your client enters text at 8 am, that
time will appear in this formula. The next time the client performs
any calculation, the NOW() function will update to the current system
time, and you'll lose data integrity.

The only solution I can think of is to write some VBA code that will
copy that cell and paste it as a value- easily enough done, but it
depends on your familiarity with this. How comfortable are you with
that idea?

Dave O

Thanks so much for trying to help!
Unfortunately I'm not at all familiar with VBA....any other suggestions?

THanks again
Cassandra

"Dave O" wrote:

Hi, Cassandra-
This formula worked:
=IF(AND(C5<"",ISTEXT(C5)),NOW(),"")
It checks to see that the cell in question is both text and non-blank
and returns the time, or a blank if that condition is not true.

HOWEVER... the problem I see is the NOW() function evaluates
differently as time passes. If your client enters text at 8 am, that
time will appear in this formula. The next time the client performs
any calculation, the NOW() function will update to the current system
time, and you'll lose data integrity.

The only solution I can think of is to write some VBA code that will
copy that cell and paste it as a value- easily enough done, but it
depends on your familiarity with this. How comfortable are you with
that idea?

No other ideas that are automated. To set it up with VBA you'd need to
determine the range of cells where comments are expected, and trigger
an event when one of those cells is changed. When that event triggers,
the code would insert the current date and time in the cell immediately
adjacent.

What is the tab name where the user is entering comments, and what is
the range of cells where this is likely to happen? It can be all of
column B, for instance, if that's the correct answer, just need to have
a range.

I have an answer for you, but it may take a while: I"m off to lunch and
then I have a series of meetings.

Dave O

You are an angel....thanks so much for your time but I think I have it
working using a circular reference....

I checked the iteration box under the tools/options/calculations menu and
set up this circular reference

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

Now whenever I enter a comment in B, I get a static time/date stamp in A.
Haven't had any problems so far.

Thanks again
Cassandra

"Dave O" wrote:

No other ideas that are automated. To set it up with VBA you'd need to
determine the range of cells where comments are expected, and trigger
an event when one of those cells is changed. When that event triggers,
the code would insert the current date and time in the cell immediately
adjacent.

What is the tab name where the user is entering comments, and what is
the range of cells where this is likely to happen? It can be all of
column B, for instance, if that's the correct answer, just need to have
a range.

I have an answer for you, but it may take a while: I"m off to lunch and
then I have a series of meetings.

Dave O

Maybe you could help me with one other question?
I inserted a word document using insert object into a worksheet. Now,
though, whenever I try to print it there is a border around the document. Do
you know how to get rid of this? Really appreciate any suggestions...

Thanks again
Cassandra

"cassandra112" wrote:

Dave O

You are an angel....thanks so much for your time but I think I have it
working using a circular reference....

I checked the iteration box under the tools/options/calculations menu and
set up this circular reference

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

Now whenever I enter a comment in B, I get a static time/date stamp in A.
Haven't had any problems so far.

Thanks again
Cassandra

"Dave O" wrote:

No other ideas that are automated. To set it up with VBA you'd need to
determine the range of cells where comments are expected, and trigger
an event when one of those cells is changed. When that event triggers,
the code would insert the current date and time in the cell immediately
adjacent.

What is the tab name where the user is entering comments, and what is
the range of cells where this is likely to happen? It can be all of
column B, for instance, if that's the correct answer, just need to have
a range.

I have an answer for you, but it may take a while: I"m off to lunch and
then I have a series of meetings.

Hey, Cassandra-
The circular reference idea never occurred to me- I've never used it
before. I've spent so much time avoiding circ refs that deliberately
creating one didn't even occur to me. Does the circular reference
warning appear on the spreadsheet? Seems that might put the client
off.

Have to disappoint you again- I can't answer the border question. I
did a search but couldn't find any answers.

Hi

I know....crazy but it seems to be working. If you check the iteration box
under tools/options/calculations the reference warning doesn't come
up.....don't know if this willl all have crazy repurcutions down the line but
i'm crossing my fingers as i'm testing it.

I found the answer to my other question.....right clicking on the word
insert brings up formatting options.

THanks again for everything!
Cassandra

"Dave O" wrote:

Hey, Cassandra-
The circular reference idea never occurred to me- I've never used it
before. I've spent so much time avoiding circ refs that deliberately
creating one didn't even occur to me. Does the circular reference
warning appear on the spreadsheet? Seems that might put the client
off.

Have to disappoint you again- I can't answer the border question. I
did a search but couldn't find any answers.