?
Solved

Excel help. I need a formula or directions

Posted on 2014-02-07
4
Medium Priority
?
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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 …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

777 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