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

phillipcook

Board Regular
Joined
Jun 25, 2015
Messages
87
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?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
As soon as you select anything from the dropdown, the formula is gone, you can't retain a formula in the same cell as a dropdown.

The best you would be able to do would be to use a worksheet change event (vba) to write the formula when the cell contents are cleared.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
Do the dropdowns ever need to be changed independently of W5? Originally I took it from your formula that there might be other options not used in the formula, if that is not the case then you could simply just remove the dropdowns from all except W5, change the formula to =T(W5) then lock the other cells to prevent the formula being deleted, no vba required.
 

phillipcook

Board Regular
Joined
Jun 25, 2015
Messages
87

ADVERTISEMENT

Do the dropdowns ever need to be changed independently of W5? Originally I took it from your formula that there might be other options not used in the formula, if that is not the case then you could simply just remove the dropdowns from all except W5, change the formula to =T(W5) then lock the other cells to prevent the formula being deleted, no vba required.
Unfortunately, yes, they do have other options in the drop down list that can be selected and not all site are done each time.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
See if this does what you need, I've used a different formula but it will do the same thing as yours.
I've set the code to apply the formula empty cells in W6:W10 (third line), you will need to edit this do fit the actual range required.
The code needs to go into the correct worksheet module. To locate this, select the sheet in excel, right click the tab then click 'view code'.
This will run automatically when you clear the contents of one of the cells, however it will not enter the formula into cells which are already empty.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, rng As Range
Set rng = Range("W6:W10") ' range for combined formula and dropdown.
If Intersect(Target, rng) Is Nothing Then Exit Sub
With Application
    .EnableEvents = False
    .ScreenUpdating = False

For Each c In Intersect(Target, rng)
    If c.Value = "" Then c.FormulaArray = "=IF(OR(W5={""Fortnightly"",""Monthly"",""Quarterly"",""Annually""}),W5,"""")"
Next

    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub
 

phillipcook

Board Regular
Joined
Jun 25, 2015
Messages
87

ADVERTISEMENT

Hi Jason

Apologies on the late reply as I didn't actually see that you replied till today.

I have added the VBA to the sheet and changed it to how I think I'm meant to to the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, rng As Range
Set rng = Range("R9") ' range for combined formula and dropdown.
If Intersect(Target, rng) Is Nothing Then Exit Sub
With Application
.EnableEvents = False
.ScreenUpdating = False

For Each c In Intersect(Target, rng)

If c.Value = "" Then c.FormulaArray = "=IF(OR(W5={""Fortnightly"",""Monthly"",""Quarterly"",""Annually""}),W5,"""")"
Next

.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

What is happening is that the cell isn't auto populating with the W5 value, it remains blank, however when I select something from the drop down list and delete it back out again, i get the debug error message and it is highlighting the following point:

If c.Value = "" Then c.FormulaArray = "=IF(OR(W5={""Fortnightly"",""Monthly"",""Quarterly"",""Annually""}),W5,"""")"

Not really sure what the error is.

With the sheet itself D8:X8 and D9:X9 will have slightly different variations of the formula as the sites have different visiting frequencies so their will be 22 different variances on the VBA im guessing, but if i can get one to work, the modification should be easy.

Is there a way to post the worksheet on here so that you can see what I am doing with a lot more ease?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
It should work fine, I just tried it and there is no error. Is your sheet protected?

The edit that you have made means that the formula is only going into a single cell, R9. Is that correct?

If the formula is not already in the cell to start with then it will not auto-populate, the code applies the formula when you delete what is in the cell, it doesn't apply it to cells that are already empty.

For the different variances, there is a way to do that without repeating everything, I'll look at that more once you get the single version working.
 

phillipcook

Board Regular
Joined
Jun 25, 2015
Messages
87
R9 is not currently locked as this prevents the drop down menu from functioning. I also have the R9 cell empty now apart from the drop down list function with the macros enabled and the VBA in the file. What I am seeing is that when W5 is changed to one of the frequency options, R9 is remaining blank. if i use the drop down menu in R9 and manually change it to a desired frequency, the VBA is okay with that (which is what we want it to let us do), however its just not auto populating the required frequency and on a rare occasion, if I delete the drop down selection out of the box it comes up with an error trying to get me to debug the issue, however, i just tried to get it to repeat the debug issue, but its now not coming up with the error.

The cells in rows 8,9,10 will normally be blank until data is selected from the drop down menu or the auto populate formula (VBA) based of the value in W5.

I'm not sure if this is a factor or not, but my cells have been merged, so W5 is technically W5:Y5 and R9 is technically R9:S9.

The auto populating formulas that I have in the sheet are as follows:
W5:Y5 - Drop down menu that contains "Fortnightly", "Monthly", "Quarterly" and "Annually"
D8:E8 - =IF(OR(W5="Quarterly",W5="Annually"),"Routine","")
H8:I8 - =IF(OR(W5="Monthly",W5="Quarterly",W5="Annually"),"Routine","")
J8:K8 - =IF(OR(W5="Quarterly",W5="Annually"),"Routine","")
L8:M8 - =IF(OR(W5="Monthly",W5="Quarterly",W5="Annually"),"Routine","")
N8:O8 - =IF(OR(W5="Monthly",W5="Quarterly",W5="Annually"),"Routine","")
P8:Q8 - =IF(OR(W5="Quarterly",W5="Annually"),"Routine","")
R8:S8 - =IF(OR(W5="Fortnightly",W5="Monthly",W5="Quarterly",W5="Annually"),"Routine","")
T8:U8 - =IF(OR(W5="Monthly",W5="Quarterly",W5="Annually"),"Routine","")
V8:W8 - =IF(OR(W5="Fortnightly",W5="Monthly",W5="Quarterly",W5="Annually"),"Routine","")
X8:Y8 - =IF(OR(W5="Fortnightly",W5="Monthly",W5="Quarterly",W5="Annually"),"Routine","")
D9:E9 - =IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly",""))
H9:I9 - =IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly","")))
J9:K9 - =IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly",""))
L9:M9 - =IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly","")))
N9:O9 - =IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly","")))
P9:Q9 - =IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly",""))
R9:S9 - =IF(W5="Fortnightly","Fortnightly",IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Annually","")))) - CURRENTLY DELETED DUE TO ADDING THE VBA
T9:U9 - =IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly","")))
V9:W9 - =IF(W5="Fortnightly","Fortnightly",IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly",""))))
X9:Y9 - =IF(W5="Fortnightly","Fortnightly",IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly",""))))
D10:E10 - =IF(OR(W5="Quarterly",W5="Annually"),"YES","")
H10:I10 - =IF(OR(W5="Monthly",W5="Quarterly",W5="Annually"),"YES","")
J10:K10 - =IF(OR(W5="Quarterly",W5="Annually"),"YES","")
L10:M10 - =IF(OR(W5="Monthly",W5="Quarterly",W5="Annually"),"YES","")
N10:O10 - =IF(OR(W5="Monthly",W5="Quarterly",W5="Annually"),"YES","")
P10:Q10 - =IF(OR(W5="Quarterly",W5="Annually"),"YES","")
R10:S10 - =IF(OR(W5="Fortnightly",W5="Monthly",W5="Quarterly",W5="Annually"),"YES","")
T10:U10 - =IF(OR(W5="Monthly",W5="Quarterly",W5="Annually"),"YES","")
V10:W10 - =IF(OR(W5="Fortnightly",W5="Monthly",W5="Quarterly",W5="Annually"),"YES","")
X10:Y10 - =IF(OR(W5="Fortnightly",W5="Monthly",W5="Quarterly",W5="Annually"),"YES","")
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
Taking a different approach to the task, this appears to work with merged cells.
VBA Code:
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.Value = [=IF(OR(W5="Quarterly",W5="Annually"),"Routine","")]
        Case "H8", "L8", "N8", "T8"
            Target.Value = [=IF(OR(W5="Monthly",W5="Quarterly",W5="Annually"),"Routine","")]
        Case "R8", "V8", "X8"
            Target.Value = [=IF(OR(W5="Fortnightly",W5="Monthly",W5="Quarterly",W5="Annually"),"Routine","")]
    
        Case "D9", "J9", "P9"
            Target.Value = [=IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly","")]
        Case "H9", "L9", "N9", "T9"
            Target.Value = [=IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly","")))]
        Case "R9"
            Target.Value = [=IF(W5="Fortnightly","Fortnightly",IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Annually",""))))]
        Case "V9", "X9"
            Target.Value = [=IF(W5="Fortnightly","Fortnightly",IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly",""))))]
        
        Case "D10", "J10", "P10"
            Target.Value = [=IF(OR(W5="Quarterly",W5="Annually"),"YES","")]
        Case "H10", "L10", "N10", "T10"
            Target.Value = [=IF(OR(W5="Monthly",W5="Quarterly",W5="Annually"),"YES","")]
        Case "R10", "V10", "X10"
            Target.Value = [=IF(OR(W5="Fortnightly",W5="Monthly",W5="Quarterly",W5="Annually"),"YES","")]
    End Select
End If
Application.EnableEvents = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,503
Members
417,030
Latest member
baqer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top