Solved

Excel help. I need a formula or directions

Posted on 2014-02-07
4
279 Views
Last Modified: 2014-02-07
Need a formula or directions to force a multiple choice drop down list in column G if the adjacent cell in column F=0.

On the same sheet I need a formula or directions where if a cell in column M=N than the adjacent cell in Rowe N can be edited, if M=Y then adjacent cell in N cannot be edited.
0
Comment
Question by:Mcottuli
  • 2
  • 2
4 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39842160
I believe you can do what you request using a combination of worksheet protection and a Worksheet_Change macro.

If you protect the worksheet, you can prevent data entry in cells that are locked. You will need to unlock all cells that you want the user to be able to change prior to protecting the worksheet. You do this by selecting those cells, then using the Home...Format Cells...Lock Cells menu item. This is a toggle, so you click it once to unlock the cells, then click it again to lock them. You want those cells unlocked.

The Worksheet_Change macro runs automatically when the user enters data in the worksheet. It can be designed to watch cells in column F and M for inputs. If in F, then it either adds a data validation dropdown to the adjacent cell or removes it. If in M, it either locks the adjacent cell in column N, or unlocks it.

'This code must go in the worksheet code pane. It won't work at all if installed anywhere else!
Private Sub Worksheet_Activate()
Me.Protect UserInterfaceOnly:=True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ As Range
Set targ = Range("F2:F1000")
Set targ = Intersect(targ, Target)
If Not targ Is Nothing Then
    For Each cel In targ.Cells
        With cel.Offset(0, 1).Validation
            .Delete
            If cel.Value = 0 Then
                .Add xlValidateList, Formula1:="Yes,No"
            End If
        End With
    Next
End If

Set targ = Range("M2:M1000")
Set targ = Intersect(targ, Target)
If Not targ Is Nothing Then
    For Each cel In targ.Cells
        Select Case UCase(cel.Value)
        Case "Y"
            cel.Offset(0, 1).Locked = True
        Case "N"
            cel.Offset(0, 1).Locked = False
        End Select
    Next
End If
End Sub

Open in new window

'This code must be installed in the ThisWorkbook code pane. It won't work at all if installed anywhere else!
Private Sub Workbook_Open()
Worksheets("Sheet1").Protect UserInterfaceOnly:=True
End Sub

Open in new window

ColumnFandM-Q28359206.xlsm
0
 
LVL 81

Expert Comment

by:byundt
ID: 39842183
As written, the Worksheet_Change macro is watching cells in rows 2 through 1000. It is putting a dropdown with Yes or No choices in column G. And it is locking or unlocking cells in column N.

There is a Workbook_Open sub to protect the worksheet with UserInterfaceOnly property set to True so macros can make changes. A Worksheet_Activate sub also applies the UserInterfaceOnly property. I used the belt and suspenders approach so you would have a higher likelihood of success during testing on your real workbook. You wouldn't need the Worksheet_Activate sub when you release the workbook to your users.
0
 
LVL 1

Author Comment

by:Mcottuli
ID: 39842761
Thank you so much, I'll give a shot and let you know.
0
 
LVL 1

Author Comment

by:Mcottuli
ID: 39842949
Perfect, Thank you
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

785 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