Locking a formula in the same cell as a drop down list

[phillipcook]

Hi,

I have a formula in the same cell as a drop down list.
The formula I have is as follows:
=IF(W5="Fortnightly","Fortnightly",IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Annually",""))))
the idea of this formula is that I have multiple cells with drop down lists, but instead of having to select the correct value in each cell, I can set a value on W5 and all the cells will change to their correct value, however I cannot lock the cells to protect the formulas from being deleted as it prevents the drop down list from being access, but if i don't lock the cell and the user goes to delete the entry in the cell to return the drop down list to blank, it will delete the formula as well.

Can anyone help me out with a way around this issue?

 

[phillipcook]

Hi Jason

so I have added the VBA in and deleted all the formulas out of the spreadsheet, however when I change W5, it’s still not changing any of the cells in rows 8,9 or 10. Not sure if I’m doing anything wrong.
I've done the following:
> saved the file as macros enabled file
> deleted all formulas (only leaving drop down menus in rows 8,9 and 10)
> right clicked on tab-click on view code-copied and pasted code from website making sure now spaces or lines before or after code.
> left dropdown on vba window is worksheet, right box on vba window is change.
> save and return to excel sheet
> change value in W5. (But no auto change in formula)

 

[phillipcook]

I did just realise, if I press delete on the cell, it changes to the desired value and it still lets me override the VBA and select a desired value.
Very close! ?
Is there a way to change it so that it auto populates without have to press delete in the empty cell, so basically if W5 is blank, the individual cell (from rows 8-10) remain blank unless the user selects values manually from the dropdown menu, but if W5 has a value and the indervidual cells (from row 8-10) are also blank then it auto populated the cells.

basically what is there but without having to press delete in an empty cell for the population to work.

 

[jasonb75]

Oops, I only allowed for the reference cells being changed, not the dropdown in W5. That will be best done by going back to formulas in the cells rather than evaluating the change in the code. This should work but I haven't tested it. Note that each cell would need to be changed once for it to take effect (deleting the empty strings as before should work).

Once each of the cells has been changed once, it should work automatically from then on.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target(1, 1).Value = "" Then
    Select Case Target(1, 1).Address(0, 0)
        Case "D8", "J8", "P8"
            Target.Formula = "=IF(OR(W5=""Quarterly"",W5=""Annually""),""Routine"","""")"
        Case "H8", "L8", "N8", "T8"
            Target.Formula = "=IF(OR(W5=""Monthly"",W5=""Quarterly"",W5=""Annually""),""Routine"","""")"
        Case "R8", "V8", "X8"
            Target.Formula = "=IF(OR(W5=""Fortnightly"",W5=""Monthly"",W5=""Quarterly"",W5=""Annually""),""Routine"","""")"
    
        Case "D9", "J9", "P9"
            Target.Formula = "=IF(W5=""Quarterly"",""Quarterly"",IF(W5=""Annually"",""Quarterly"","""")"
        Case "H9", "L9", "N9", "T9"
            Target.Formula = "=IF(W5=""Monthly"",""Monthly"",IF(W5=""Quarterly"",""Quarterly"",IF(W5=""Annually"",""Quarterly"","""")))"
        Case "R9"
            Target.Formula = "=IF(W5=""Fortnightly"",""Fortnightly"",IF(W5=""Monthly"",""Monthly"",IF(W5=""Quarterly"",""Quarterly"",IF(W5=""Annually"",""Annually"",""""))))"
        Case "V9", "X9"
            Target.Formula = "=IF(W5=""Fortnightly"",""Fortnightly"",IF(W5=""Monthly"",""Monthly"",IF(W5=""Quarterly"",""Quarterly"",IF(W5=""Annually"",""Quarterly"",""""))))"
        
        Case "D10", "J10", "P10"
            Target.Formula = "=IF(OR(W5=""Quarterly"",W5=""Annually""),""YES"","""")"
        Case "H10", "L10", "N10", "T10"
            Target.Formula = "=IF(OR(W5=""Monthly"",W5=""Quarterly"",W5=""Annually""),""YES"","""")"
        Case "R10", "V10", "X10"
            Target.Formula = "=IF(OR(W5=""Fortnightly"",W5=""Monthly"",W5=""Quarterly"",W5=""Annually""),""YES"","""")"
    End Select
End If
Application.EnableEvents = True
End Sub