I have a spreadsheet where users enter text in one field followed by a date
in the next (text in col. B, dates in col. C - not always "todays" date)
Some users enter the text but forget about the date, this causes problems
with stats. How can I force users to always enter the date in column C if
they have entered text in column B?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200512/1

Hi,

Here are some suggestions. Let's suppose that your data start in Row 2.

1. If column D is not already empty, insert a new column between the
current C and D, and enter the following formula in D2 and auto-fill the
column. When a user enters a text in B2, D2 would show the warning "<--Enter
Date")

OR

2. Enter the following formula in C2 and autofill.
=IF(ISBLANK(B26),"","Enter date")
When a user enters a text in B2, C2 would show "Enter date". ANY kind of
entry in C2 would overwrite the formula (so this is a one-time solution for
each cell in column C)
=IF(ISBLANK(B2)+ISNUMBER(C2)=0,"<-- Enter Date","")

3. You could use this Conditional Formatting approach in conjunction with
one of the above.
Select the entire range of column C (say C2:C101), which of course would be
empty at the beginning) -- "Format" -- "Conditional Formatting" -- under
Conditon 1 "Formula Is" =ISBLANK(B2)+ISNUMBER(C2)=0 -- "Format" button
-- in "Patterns" Tab select a color for cell-shading -- "OK".
When a user enters a text in a cell column B, the column C of the
corresponding row would color up as a warning.

Regards,
B. R. Ramachandran



"Brian Hearty via OfficeKB.com" wrote:

I have a spreadsheet where users enter text in one field followed by a date
in the next (text in col. B, dates in col. C - not always "todays" date)
Some users enter the text but forget about the date, this causes problems
with stats. How can I force users to always enter the date in column C if
they have entered text in column B?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200512/1

Hi,

Sorry for posting this again. In my previous reply, a formula, for reasons
I don't know, has gone to a wrong location.

Here are some suggestions. Let's suppose that your data start in Row 2.

1. If column D is not already empty, insert a new column between the
current C and D, and enter the following formula in D2 and auto-fill the
column.

=IF(ISBLANK(B2)+ISNUMBER(C2)=0,"<-- Enter Date","")

When a user enters a text in B2, D2 would show the warning "<--Enter
Date")

OR

2. Enter the following formula in C2 and autofill.

=IF(ISBLANK(B26),"","Enter date")

When a user enters a text in B2, C2 would show "Enter date". ANY kind of
entry in C2 would overwrite the formula (so this is a one-time solution for
each cell in column C)


3. You could use this Conditional Formatting approach in conjunction with
one of the above.
Select the entire range of column C (say C2:C101), which of course would be
empty at the beginning) -- "Format" -- "Conditional Formatting" -- under
Conditon 1 "Formula Is" =ISBLANK(B2)+ISNUMBER(C2)=0 -- "Format" button
-- in "Patterns" Tab select a color for cell-shading -- "OK".
When a user enters a text in a cell column B, the column C of the
corresponding row would color up as a warning.

Regards,
B. R. Ramachandran



"Brian Hearty via OfficeKB.com" wrote:

I have a spreadsheet where users enter text in one field followed by a date
in the next (text in col. B, dates in col. C - not always "todays" date)
Some users enter the text but forget about the date, this causes problems
with stats. How can I force users to always enter the date in column C if
they have entered text in column B?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200512/1