cell lock

bam12

Well-known Member
Joined
Dec 6, 2004
Messages
983
it here way to lock a cell when a certain date is meet with a formula, so that no info can be entered in it?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,545
Office Version
  1. 365
Platform
  1. Windows
it here way to lock a cell when a certain date is meet with a formula, so that no info can be entered in it?
There probably is but you would need to spell this out more clearly. I don't know what you mean by "when a certain date is meet with a formula".
 

bam12

Well-known Member
Joined
Dec 6, 2004
Messages
983
answer

What i meant is this

in cell C4i have a date let say 5/1/1994

in d1

I dont want to ba able to enter data in this cell when today date is eqaul to or greater then C4

i have seen VBA for this but was wondering if able to do with a formula
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,545
Office Version
  1. 365
Platform
  1. Windows
Try this:
1. Select D1
2. Data|Validation...|Settings|Allow: Custom|Formula:
Code:
=TODAY()<C4|OK

Note: If you want to apply similar Data Validation to a lot of cells then it would be better to put the formula =TODAY() into a blank cell somewhere (say Z1) then in the Data Validation Formula use something like
Code:
=$Z$1<C4
 

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001

ADVERTISEMENT

try

DATA >> VALIDATION

select DATE then is less than and enter =C4 in the end date

you can then use the input and error alerts box to enter information

HTH
 

bam12

Well-known Member
Joined
Dec 6, 2004
Messages
983
question

how about if i want it to lock on the next occurrance of 5/1, which would be 2007
 

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
just alter the part in PeterSSS formula from <C4

to

Code:
=TODAY()<5/1/2007
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,545
Office Version
  1. 365
Platform
  1. Windows
.. or if you want the next occurrance based on what is in C4:

1. If you have Analysis ToolPak installed try =TODAY()<EDATE(C4,12)

2. If you do not have Analysis ToolPak installed and don't want to install it, then try =TODAY()<DATE(YEAR(C4)+1,MONTH(C4),DAY(C4))

Note: To check if you have Analysis ToolPak: Tools|Add-Ins... and see if Analysis ToolPak is ticked. If not, do so, and click OK.
 

Forum statistics

Threads
1,137,205
Messages
5,680,184
Members
419,887
Latest member
Vasokir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top