I am using Excel 2003. I have built a spreadsheet that is formatted with 12
columns across (one for each month of the year) and then 50-75 rows of data
down that will have activity recorded within each of the twelve month
columns. I have a status row directly below the column headings row that will
indicate whether the month is "open" (meaning open to being updated) or
"closed" (meaning the month is over and no more posting of data is allowed).
What I am trying to do is provide the capability that as the status for a
month is changed to "Closed", all the cells in that month column within each
of the rows would be automatically locked so the data could no longer be
altered. I tried Conditional Formatting, but that seems to only work for
colors, fonts, etc. I'm sure it can be done with a macro of some sorts, but
that seems overly complicated. Has anyone ever faced this problem and come up
with a somewhat simple solution?

Hi Mike,

Data validation should do it.

Assuming that 'Open' (or whatever to allow entry) is in cell A2 for column
A. Select the cells below cell A2 where entries can be made. (Rest of column
if you like).

Select menu item Data-Validation
Select tab Settings
Under Allow click drop down and select Custom
In formula insert =$A$2="Open"
Click OK

You can now only enter data in column A if A2 has the word open. (Note that
in the formula A2 must be in absolute format with the $ signs.)

You might want to protect row 2 with a password so only you can change it to
closed or whatever.

Regards,

OssieMac

"MikeTVC" wrote:

I am using Excel 2003. I have built a spreadsheet that is formatted with 12
columns across (one for each month of the year) and then 50-75 rows of data
down that will have activity recorded within each of the twelve month
columns. I have a status row directly below the column headings row that will
indicate whether the month is "open" (meaning open to being updated) or
"closed" (meaning the month is over and no more posting of data is allowed).
What I am trying to do is provide the capability that as the status for a
month is changed to "Closed", all the cells in that month column within each
of the rows would be automatically locked so the data could no longer be
altered. I tried Conditional Formatting, but that seems to only work for
colors, fonts, etc. I'm sure it can be done with a macro of some sorts, but
that seems overly complicated. Has anyone ever faced this problem and come up
with a somewhat simple solution?

OssieMac,

Thanks so much for your response. It works great. I even eliminated the "$"
before the column name and was able to drag the formula across all 12
columns...this is great. Thanks for taking the time to respond.
Mike

"OssieMac" wrote:

Hi Mike,

Data validation should do it.

Assuming that 'Open' (or whatever to allow entry) is in cell A2 for column
A. Select the cells below cell A2 where entries can be made. (Rest of column
if you like).

Select menu item Data-Validation
Select tab Settings
Under Allow click drop down and select Custom
In formula insert =$A$2="Open"
Click OK

You can now only enter data in column A if A2 has the word open. (Note that
in the formula A2 must be in absolute format with the $ signs.)

You might want to protect row 2 with a password so only you can change it to
closed or whatever.

Regards,

OssieMac

"MikeTVC" wrote:

I am using Excel 2003. I have built a spreadsheet that is formatted with 12
columns across (one for each month of the year) and then 50-75 rows of data
down that will have activity recorded within each of the twelve month
columns. I have a status row directly below the column headings row that will
indicate whether the month is "open" (meaning open to being updated) or
"closed" (meaning the month is over and no more posting of data is allowed).
What I am trying to do is provide the capability that as the status for a
month is changed to "Closed", all the cells in that month column within each
of the rows would be automatically locked so the data could no longer be
altered. I tried Conditional Formatting, but that seems to only work for
colors, fonts, etc. I'm sure it can be done with a macro of some sorts, but
that seems overly complicated. Has anyone ever faced this problem and come up
with a somewhat simple solution?

OssieMac,
I notice that even though I cannot change the value of a cell once the data
validation is turned on, it does not prevent me from doing a "clear contents"
of the cell value, which would still cause problems since it changes teh
totals for the month once it has been "closed" Is there a way to prevent that?

"MikeTVC" wrote:

OssieMac,

Thanks so much for your response. It works great. I even eliminated the "$"
before the column name and was able to drag the formula across all 12
columns...this is great. Thanks for taking the time to respond.
Mike

"OssieMac" wrote:

Hi Mike,

Data validation should do it.

Assuming that 'Open' (or whatever to allow entry) is in cell A2 for column
A. Select the cells below cell A2 where entries can be made. (Rest of column
if you like).

Select menu item Data-Validation
Select tab Settings
Under Allow click drop down and select Custom
In formula insert =$A$2="Open"
Click OK

You can now only enter data in column A if A2 has the word open. (Note that
in the formula A2 must be in absolute format with the $ signs.)

You might want to protect row 2 with a password so only you can change it to
closed or whatever.

Regards,

OssieMac

"MikeTVC" wrote:

I am using Excel 2003. I have built a spreadsheet that is formatted with 12
columns across (one for each month of the year) and then 50-75 rows of data
down that will have activity recorded within each of the twelve month
columns. I have a status row directly below the column headings row that will
indicate whether the month is "open" (meaning open to being updated) or
"closed" (meaning the month is over and no more posting of data is allowed).
What I am trying to do is provide the capability that as the status for a
month is changed to "Closed", all the cells in that month column within each
of the rows would be automatically locked so the data could no longer be
altered. I tried Conditional Formatting, but that seems to only work for
colors, fonts, etc. I'm sure it can be done with a macro of some sorts, but
that seems overly complicated. Has anyone ever faced this problem and come up
with a somewhat simple solution?

Hi Mike,

I thought that if Ignore blank was unchecked then it would not allow the
clear but I tested this and it still allows it. I even tried some alternative
formulas but to no avail.

Sorry that I cannot provide the answer so I suggest that you start a new
thread and someone might be able to provide the answer for you.

Also it is always a good idea to include your version of xl when you post
because it can sometimes make a difference to answers.

--
Regards,

OssieMac


"MikeTVC" wrote:

OssieMac,
I notice that even though I cannot change the value of a cell once the data
validation is turned on, it does not prevent me from doing a "clear contents"
of the cell value, which would still cause problems since it changes teh
totals for the month once it has been "closed" Is there a way to prevent that?

"MikeTVC" wrote:

OssieMac,

Thanks so much for your response. It works great. I even eliminated the "$"
before the column name and was able to drag the formula across all 12
columns...this is great. Thanks for taking the time to respond.
Mike

"OssieMac" wrote:

Hi Mike,

Data validation should do it.

Assuming that 'Open' (or whatever to allow entry) is in cell A2 for column
A. Select the cells below cell A2 where entries can be made. (Rest of column
if you like).

Select menu item Data-Validation
Select tab Settings
Under Allow click drop down and select Custom
In formula insert =$A$2="Open"
Click OK

You can now only enter data in column A if A2 has the word open. (Note that
in the formula A2 must be in absolute format with the $ signs.)

You might want to protect row 2 with a password so only you can change it to
closed or whatever.

Regards,

OssieMac

"MikeTVC" wrote:

I am using Excel 2003. I have built a spreadsheet that is formatted with 12
columns across (one for each month of the year) and then 50-75 rows of data
down that will have activity recorded within each of the twelve month
columns. I have a status row directly below the column headings row that will
indicate whether the month is "open" (meaning open to being updated) or
"closed" (meaning the month is over and no more posting of data is allowed).
What I am trying to do is provide the capability that as the status for a
month is changed to "Closed", all the cells in that month column within each
of the rows would be automatically locked so the data could no longer be
altered. I tried Conditional Formatting, but that seems to only work for
colors, fonts, etc. I'm sure it can be done with a macro of some sorts, but
that seems overly complicated. Has anyone ever faced this problem and come up
with a somewhat simple solution?

Ignore blanks is if you validate one cell referencing another cell and if
the other cell is empty validation is not applied in the validation cell,
you can always clear whatever is in the validated cell itself.


--


Regards,


Peo Sjoblom



"OssieMac" wrote in message
...
Hi Mike,

I thought that if Ignore blank was unchecked then it would not allow the
clear but I tested this and it still allows it. I even tried some
alternative
formulas but to no avail.

Sorry that I cannot provide the answer so I suggest that you start a new
thread and someone might be able to provide the answer for you.

Also it is always a good idea to include your version of xl when you post
because it can sometimes make a difference to answers.

--
Regards,

OssieMac


"MikeTVC" wrote:

OssieMac,
I notice that even though I cannot change the value of a cell once the
data
validation is turned on, it does not prevent me from doing a "clear
contents"
of the cell value, which would still cause problems since it changes teh
totals for the month once it has been "closed" Is there a way to prevent
that?

"MikeTVC" wrote:

OssieMac,

Thanks so much for your response. It works great. I even eliminated the
"$"
before the column name and was able to drag the formula across all 12
columns...this is great. Thanks for taking the time to respond.
Mike

"OssieMac" wrote:

Hi Mike,

Data validation should do it.

Assuming that 'Open' (or whatever to allow entry) is in cell A2 for
column
A. Select the cells below cell A2 where entries can be made. (Rest of
column
if you like).

Select menu item Data-Validation
Select tab Settings
Under Allow click drop down and select Custom
In formula insert =$A$2="Open"
Click OK

You can now only enter data in column A if A2 has the word open.
(Note that
in the formula A2 must be in absolute format with the $ signs.)

You might want to protect row 2 with a password so only you can
change it to
closed or whatever.

Regards,

OssieMac

"MikeTVC" wrote:

I am using Excel 2003. I have built a spreadsheet that is formatted
with 12
columns across (one for each month of the year) and then 50-75 rows
of data
down that will have activity recorded within each of the twelve
month
columns. I have a status row directly below the column headings row
that will
indicate whether the month is "open" (meaning open to being
updated) or
"closed" (meaning the month is over and no more posting of data is
allowed).
What I am trying to do is provide the capability that as the status
for a
month is changed to "Closed", all the cells in that month column
within each
of the rows would be automatically locked so the data could no
longer be
altered. I tried Conditional Formatting, but that seems to only
work for
colors, fonts, etc. I'm sure it can be done with a macro of some
sorts, but
that seems overly complicated. Has anyone ever faced this problem
and come up
with a somewhat simple solution?