Enable cell locking based on value in another cell

[JC2442]

Hello,

First time poster, frequent reader and hoping for some help.

Is there a way in Excel (2010) to lock data from being entered into cells based on the value in another cell? Here is an example:

Row 4 contains dates from 2015-2030 as a header starting at column C (C4 and on) that represents years of revenue.
Column B contains dates as well, this date can be any year but this becomes the driver for the data input in columns C through X. Column B contains the "Delivery Date"

Data is input in Columns C and so forth. The issue becomes that let's say that the date in C4 is 2016 and this is the beginning year. Obviously there should be no inputs for 2015 (C5) and the data should start to be input for this year at 2016 (C6). Often people just begin filling in the first available year not looking at what the year actually is.

Is there a way to add a formula or some logic/protection to prevent inputs in previous rows based on the values in column B? So if the value in column B is XXXX than there can be no inputs in cells less than that value? Appreciate the help and apologies if my phrasing is lackluster.

[Bernie Deitrick]

You can use Data Validation with a custom formula that can basically do anything that you can write a TRUE/FALSE formula for. Post a workbook with examples of what you want to allow, and what you would not want entered, with your logic. Your description is a bit unclear.....

[JC2442]

I've added a work book for better clarity. Row 5 (Sneakers) contains accurate data because the revenue is populating in the year of the Delivery Date. Row 6 (Racquet) is incorrect as there can be no revenue in 2016 or 2017 because the Delivery Date is 2018. The goal here would be for whatever is in Column B to prevent inputs in columns that are less than that value. You cannot input revenue data in 2017 if your Delivery Date is not until 2018. Does this make more sense?

[vikaskamble87]

I have a simple logic.. hope it works for you.. we can use conditional formating option for highlighting the text boxes which should not be filled. use the formula =IF(C$4<$B5,"True","False") in the conditional formating..
The sample is attached.

[JC2442]

I have a simple logic.. hope it works for you.. we can use conditional formating option for highlighting the text boxes which should not be filled. use the formula =IF(C$4<$B5,"True","False") in the conditional formating..
The sample is attached.

This was a good idea but the spreadsheet I supplied is a drastically simplified version of what I am working on. There is quite a bit of conditional formatting already on this spreadsheet and it is quite large. I was hoping to be able to lock the cells based on the date in a specific column. Also a lot of people don't care for reading instructions.

[Bernie Deitrick]

Select C5:R7 of your example workbook, select Data Validation/ Custom, and use the formula

=C$4<=$B5

Though maybe change to

=C$4>=$B5

since I'm not certain of your logic and your example was unclear.

[vikaskamble87]

Try this file.. i have used data validation.

the cells below the year mentioned in Column will be locked.