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","")