[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Locking cells dependant on value in another cell

Posted on 2011-04-19
11
Medium Priority
?
583 Views
Last Modified: 2012-05-11
I would like to lock cells in a spreadsheet against editing dependant on a value in another cell.  Here is what I have:
           Col A      Col B
R1      Date 1    Value1
R2      Date 2    Value2
R3      Date 3    Value4
         etc etc

Basically I want to lock the changing of values in Column B if the corresponding date in Column A is before TODAY().

Is there any way that I can do this?

I would prefer to use basic Excel functions rather than macro/VB if possible.

Regards

Richard
0
Comment
Question by:rltomalin
  • 5
  • 4
  • 2
11 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35423046
You will need a macro to do that! You need to protect your sheet so that you can protect thoose cells. Basically it needs to check the value of cells in column A and compare it with today's date and if they are before today, lock the cell on column B, if they aren't, unlock the value on column B. This needs to be done using a macro!

jppinto
0
 

Author Comment

by:rltomalin
ID: 35423091
Thanks jppinto

OK - could you suggest how I do that - I'm not so familiar with creating and using macros.

Regards

Richard
0
 
LVL 33

Accepted Solution

by:
jppinto earned 1000 total points
ID: 35423112
Here's the code to put on your sheet. I've attached a sample file. Hope that you understand the logics here.

jppinto
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cll As Range
Dim rng As Range
Set rng = Range("A1:A10")

ActiveSheet.Unprotect Password:="pass"

    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        For Each cll In rng
            If cll.Value < Now() Then
                cll.Offset(0, 1).Locked = True
            Else
                cll.Offset(0, 1).Locked = False
            End If
        Next
    End If
ActiveSheet.Protect Password:="pass"

End Sub

Open in new window

ProtectColumn.xlsm
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 33

Expert Comment

by:jppinto
ID: 35423122
I've putted range A1:A10 for testing purpose but you should change it to check all of column A like this:

Set rng = Range("A:A")

and you can change also this line:

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then

for this:

If Not Intersect(Target, rng) Is Nothing Then

jppinto
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 1000 total points
ID: 35423139
You can use data validation to achieve this. See attached file

Saqib
Data-validation-.xls
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35423169
Saqib, I like your solution also but it's easy to write on the values on column B, they aren't really locked, you just need to copy a value from another cell on top of the value that should be locked and there it goes the Data Validation protection! To get a full proof solution, you will need to lock the cells by protecting your sheet...

jppinto
0
 

Author Comment

by:rltomalin
ID: 35423625
Thanks for the suggestions.  Have a meeting now - so will look at this later - probably tomorrow.

Regards

Richard
0
 

Author Comment

by:rltomalin
ID: 35432856
Thanks both of you for your suggestions.  While I appreciate the shortcomings of the validation solution, it would be sufficient for my purposes.
However, the formula in the sample references an absolute cell.  I have 365 daya to assign this to and I was hoping to somehow simply replicate the settings for the whole column, using a relative reference (ie the date of that cell).  Is there a way that I can do that - or do I need to modify the validation rule each time for each cell in the column.

Thanks again
Richard
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35433216
The cell C1 is dependent on B1
The cell C2 is dependent on B2
The cell C3 is dependent on B3

and will continue if you copy down. There is no absolute cell referred.

Saqib
0
 

Author Comment

by:rltomalin
ID: 35433568
How right you are!!  I was sure that I copied the cells down and the reference in the validation formula stayed the same - but I must have been mistaken because I just tried it again and it is fine.
Regards

Richard
0
 

Author Closing Comment

by:rltomalin
ID: 35433595
Although I used the validation solution, I accept that the macro is a more complete and robust solution.  Both solutions were given promptly, so I have shared the points equally.
Regards
Richard
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question