A Microsoft Word forum. Microsoft Office Word Forum - WordBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » Microsoft Office Word Forum - WordBanter forum » Microsoft Word Newsgroups » Tables
Site Map Home Authors List Search Today's Posts Mark Forums Read Web Partners

Formula for non blank cell



 
 
Thread Tools Display Modes
  #1  
Old December 4th 08, 12:23 AM posted to microsoft.public.word.tables
Penny Miller
external usenet poster
 
Posts: 2
Default Formula for non blank cell

I hope this makes sense... I'm working with a department that uses word to
keep track of when employees are doing their designated duties by system
date/time to make sure they aren't fudging the time.

I've created a document that has two different tables in it and the first
table (7 columns) can be un protected with no worries. However, the second
table (2 columns) needs to have cell A1 protected and if a user keys in any
text in B1 I need it to add the current date/time to A1 once they hit the
enter/tab key.

If B1 isnot blank then A1=CREATEDATE \@ "yyyy-MM-dd" \@ "HH:mm:ss"

Thanks

Ads
  #2  
Old December 4th 08, 04:44 AM posted to microsoft.public.word.tables
macropod[_2_]
external usenet poster
 
Posts: 2,059
Default Formula for non blank cell

Hi Penny,

This is probably best handled by referencing the formfield's bookmark, rather than the cell. That way the calculation isn't affected
by a change in table layout. Also make sure the formfield's properties are set to 'calculate on exit'.

In A1, you could then use a formula field coded like:
{IF{REF BkMrk}= "BkMrk_Default" "{CREATEDATE \@ "yyyy-MM-dd" \@ "HH:mm:ss"}" "Result if BkMrk not Default"}
whe
.. BkMrk is the formfield's bookmark name.
.. BkMrk_Default is whatever the formfield has before being filled in. If it's the default space sequence, use " *" to trap any
sequence beginning with a space (in case the user simply hits the spacebar one or more times then tabs out of the field)
.. if there's nothing to display if the user enters nothing in the formfield, you can delete "Result if BkMrk not Default" in its
entirety.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[MVP - Microsoft Word]


"Penny Miller" wrote in message ...
I hope this makes sense... I'm working with a department that uses word to keep track of when employees are doing their designated
duties by system date/time to make sure they aren't fudging the time.

I've created a document that has two different tables in it and the first table (7 columns) can be un protected with no worries.
However, the second table (2 columns) needs to have cell A1 protected and if a user keys in any text in B1 I need it to add the
current date/time to A1 once they hit the enter/tab key.

If B1 isnot blank then A1=CREATEDATE \@ "yyyy-MM-dd" \@ "HH:mm:ss"

Thanks


  #3  
Old December 4th 08, 06:20 PM posted to microsoft.public.word.tables
Penny Miller
external usenet poster
 
Posts: 2
Default Formula for non blank cell

I must have done something incorrect, when I went to fill in the formfield,
it typed in what I told it to but it didn't automattically include the
date/time in A1. Do I need to protect this portion of the document? This
is what I did:

A1 (basically, this cell looks blank unless you toggled the fied codes)
Did ctrl+f9 and inserted the code you provided (thanks by the way) and I
created a bookmark to this code called BkMrk

B1 (this cell shows that it's a form field that is shaded)
Created form field, selected Calculate on exit (the check box above it was
already checked "Fill-in enabled") and gave it the bookmark called
BkMrk_Default


"macropod" wrote in message
...
Hi Penny,

This is probably best handled by referencing the formfield's bookmark,
rather than the cell. That way the calculation isn't affected by a change
in table layout. Also make sure the formfield's properties are set to
'calculate on exit'.

In A1, you could then use a formula field coded like:
{IF{REF BkMrk}= "BkMrk_Default" "{CREATEDATE \@ "yyyy-MM-dd" \@
"HH:mm:ss"}" "Result if BkMrk not Default"}
whe
. BkMrk is the formfield's bookmark name.
. BkMrk_Default is whatever the formfield has before being filled in. If
it's the default space sequence, use " *" to trap any sequence beginning
with a space (in case the user simply hits the spacebar one or more times
then tabs out of the field)
. if there's nothing to display if the user enters nothing in the
formfield, you can delete "Result if BkMrk not Default" in its entirety.

Note: The field brace pairs (ie '{ }') for the above example are created
via Ctrl-F9 - you can't simply type them or copy & paste them from this
message.

--
Cheers
macropod
[MVP - Microsoft Word]


"Penny Miller" wrote in message
...
I hope this makes sense... I'm working with a department that uses word
to keep track of when employees are doing their designated duties by
system date/time to make sure they aren't fudging the time.

I've created a document that has two different tables in it and the first
table (7 columns) can be un protected with no worries. However, the
second table (2 columns) needs to have cell A1 protected and if a user
keys in any text in B1 I need it to add the current date/time to A1 once
they hit the enter/tab key.

If B1 isnot blank then A1=CREATEDATE \@ "yyyy-MM-dd" \@ "HH:mm:ss"

Thanks



  #4  
Old December 4th 08, 10:46 PM posted to microsoft.public.word.tables
macropod[_2_]
external usenet poster
 
Posts: 2,059
Default Formula for non blank cell

Hi Penny,

"I created a bookmark to this code called BkMrk ... Created form field ... and gave it the bookmark called BkMrk_Default"
The names 'BkMrk' and 'BkMrk_Default' I used in my reply were for illustration only, to explain what you needed to do.

Working with what you've got, 'BkMrk' (not BkMrk_Default) is the name you need to insert as the bookmark name in the formfield
properties.

Assuming your formfield starts off 'empty', try replacing "BkMrk_Default" in the field code with " *".

Yes, you do need to protect the document for forms for this to work.

--
Cheers
macropod
[MVP - Microsoft Word]


"Penny Miller" wrote in message ...
I must have done something incorrect, when I went to fill in the formfield, it typed in what I told it to but it didn't
automattically include the date/time in A1. Do I need to protect this portion of the document? This is what I did:

A1 (basically, this cell looks blank unless you toggled the fied codes)
Did ctrl+f9 and inserted the code you provided (thanks by the way) and I created a bookmark to this code called BkMrk

B1 (this cell shows that it's a form field that is shaded)
Created form field, selected Calculate on exit (the check box above it was already checked "Fill-in enabled") and gave it the
bookmark called BkMrk_Default


"macropod" wrote in message ...
Hi Penny,

This is probably best handled by referencing the formfield's bookmark, rather than the cell. That way the calculation isn't
affected by a change in table layout. Also make sure the formfield's properties are set to 'calculate on exit'.

In A1, you could then use a formula field coded like:
{IF{REF BkMrk}= "BkMrk_Default" "{CREATEDATE \@ "yyyy-MM-dd" \@ "HH:mm:ss"}" "Result if BkMrk not Default"}
whe
. BkMrk is the formfield's bookmark name.
. BkMrk_Default is whatever the formfield has before being filled in. If it's the default space sequence, use " *" to trap any
sequence beginning with a space (in case the user simply hits the spacebar one or more times then tabs out of the field)
. if there's nothing to display if the user enters nothing in the formfield, you can delete "Result if BkMrk not Default" in its
entirety.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[MVP - Microsoft Word]


"Penny Miller" wrote in message ...
I hope this makes sense... I'm working with a department that uses word to keep track of when employees are doing their
designated duties by system date/time to make sure they aren't fudging the time.

I've created a document that has two different tables in it and the first table (7 columns) can be un protected with no worries.
However, the second table (2 columns) needs to have cell A1 protected and if a user keys in any text in B1 I need it to add the
current date/time to A1 once they hit the enter/tab key.

If B1 isnot blank then A1=CREATEDATE \@ "yyyy-MM-dd" \@ "HH:mm:ss"

Thanks




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy a Table Formula to Another Cell Robert T Tables 5 January 12th 07 12:37 AM
cell addresses and formula? Susan Tables 4 June 30th 06 04:18 AM
Formula & Cell #s Carla Tables 1 September 17th 05 12:28 AM
Adding formula in the cell Wi$hma$teR Tables 0 July 19th 05 12:52 PM
I need a formula of amount of hours from two times in two cell? dmvargas29 Tables 2 November 20th 04 02:48 AM


All times are GMT +1. The time now is 04:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.