Posts by HOC

HOC · Feb 7th 2017

Re: Hide/Unhide Rows Based on a Formulated Cell

Quote from StephenR;785134

Please make sure you use code tags in future as per forum rules.

If these are as a result of a formula result, the change event will not fire. You need to use the Calculate event, though this does not relate to a specific range.

Thank you for replying, apologies not aware of what code tags are. I' m new to VBA.

Would you mind giving me an example of what I need to change in the VBA code?

The Select/Yes/No dropdown list answers are input in F10-F16

Code

I use the following formulas to calculate the overall answer:
[B]P3 [/B]COUNTIF(F10:F16,"Yes") and [B]R3 [/B]IF(AND(P3>=1,P10=0),"Significant Change","FALSE")
[B]P9[/B] COUNTIF(F10:F16,"No") and [B]R9[/B] IF(AND(P9>=1,P3=0,P10=0),"Non Significant","FALSE")
[B]P10 [/B]COUNTIF(F10:F16,"Select") and [B]R10 [/B]IF(P10>=1,"TBA")
[B]E17[/B]: IF(R$3="Significant Change","Significant Change - complete the additional materiality questions below",IF(R$9="Non Significant","Non Significant Change - Minor Local Governance applies",IF(R$10="TBA","TBA")))


Private Sub Worksheet_Change(ByVal Target As Range)
 
  If Target.Column = 5 And Target.Row = 17 And Target.Value = "Non Significant Change - Minor Local Governance applies" Then
        Rows("1:18").EntireRow.Hidden = False
        Rows("19:28").EntireRow.Hidden = True
        Rows("29").EntireRow.Hidden = False
        Rows("30").EntireRow.Hidden = True
        Rows("31:121").EntireRow.Hidden = False
        Rows("122:128").EntireRow.Hidden = True
        Rows("129").EntireRow.Hidden = False
        Rows("130:301").EntireRow.Hidden = True
       
ElseIf Target.Column = 5 And Target.Row = 17 And Target.Value = "Significant Change - complete the additional materiality questions below" Then
        Rows("1:17").EntireRow.Hidden = False
        Rows("18").EntireRow.Hidden = True
        Rows("19:26").EntireRow.Hidden = False
        Rows("27:301").EntireRow.Hidden = True


    End If
End Sub

Display More

Thank you for your time

HOC · Feb 7th 2017

Hi I am looking to see what I am doing wrong.
I have an excel template which I want to open in stages based on formulated answers. i.e E17, E26
The formulated field outputs an answer based on Yes/No scoring in other cells.
However, when the answer formulates when all questions answered the rows do not hide/unhide. I can get it working on dropdown list but really need it to calculate based on the formulated answer.
Any ideas where I am going wrong??

Code

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Target.Column = 5 And Target.Row = 17 And Target.Value = "Non Significant Change - Minor Local Governance applies" Then
        Rows("1:18").EntireRow.Hidden = False
        Rows("19:28").EntireRow.Hidden = True
        Rows("29").EntireRow.Hidden = False
        Rows("30").EntireRow.Hidden = True
        Rows("31:121").EntireRow.Hidden = False
        Rows("122:128").EntireRow.Hidden = True
        Rows("129").EntireRow.Hidden = False
        Rows("130:301").EntireRow.Hidden = True
        
ElseIf Target.Column = 5 And Target.Row = 17 And Target.Value = "Significant Change - complete the additional materiality questions below" Then
        Rows("1:17").EntireRow.Hidden = False
        Rows("18").EntireRow.Hidden = True
        Rows("19:26").EntireRow.Hidden = False
        Rows("27:301").EntireRow.Hidden = True
ElseIf Target.Column = 5 And Target.Row = 26 And Target.Value = "Significant Non Material [Standard]" Then
        Rows("1:17").EntireRow.Hidden = False
        Rows("18").EntireRow.Hidden = True
        Rows("19:27").EntireRow.Hidden = False
        Rows("28:29").EntireRow.Hidden = True
        Rows("30:82").EntireRow.Hidden = False
        Rows("83:98").EntireRow.Hidden = True
        Rows("99:121").EntireRow.Hidden = False
        Rows("122:128").EntireRow.Hidden = True
        Rows("129:300").EntireRow.Hidden = False
        Rows("301").EntireRow.Hidden = True
ElseIf Target.Column = 5 And Target.Row = 26 And Target.Value = "Significant Change - Material" Then
        Rows("1:17").EntireRow.Hidden = False
        Rows("18").EntireRow.Hidden = True
        Rows("19:26").EntireRow.Hidden = False
        Rows("27").EntireRow.Hidden = True
        Rows("28").EntireRow.Hidden = False
        Rows("29").EntireRow.Hidden = True
        Rows("30:82").EntireRow.Hidden = False
        Rows("83:98").EntireRow.Hidden = True
        Rows("99:121").EntireRow.Hidden = False
        Rows("122:128").EntireRow.Hidden = True
        Rows("129").EntireRow.Hidden = False
        Rows("300").EntireRow.Hidden = True
        Rows("301").EntireRow.Hidden = False  


End If
End Sub

Display More

HOC · Apr 8th 2016

Re: Formula Query dependent on outputs of others

Thanks, I've tested them and is working the way I need it to

HOC · Apr 8th 2016

Re: Formula Query dependent on outputs of others

That works perfectly!! Thank you so much for your help

HOC · Apr 8th 2016

Hi
I have 3 Questions A44,A45,A46 with Yes/No answer options in C44,C45,C46
I am trying to populate a formula in A47 that will show text from M2 if the answers are all No, Show text from M3 if any one of them is Yes, and show False if any one of them is unanswered.
Im going around in circles!!

example of what I tried
M7=IF(AND(C44,C45,C46=""),"NA",IF(AND(C44,C45,C46="No"),"No",IF(OR(C44,C45,C46="Yes"),"Yes")))
=IF(M7="Yes",M3,IF(M7="No",M2))

Anyone know of a simple way to do this?

HOC · Apr 7th 2016

Re: Too Many Nested Levels

Thank you so much for your help, I would never have got that on my own!! must remember for next time.

HOC · Apr 7th 2016

Re: Too Many Nested Levels

Would you have an example of Vlookup formula I haven't used this function before

HOC · Apr 7th 2016

Can anyone help me with a formula which has too many nesting levels.

I have a dropdown selection with 14 options
So my formula is if D4 = X Y or Z enter certain text in another cell

=IF(D$4=0,"Enter Dept First & Click Button Again",IF(D$4="Mortgages","MOR",IF(D$4="Deposits","DEP",IF(D$4="Cards","CAR",IF(D$4="Loans","LOA",IF(D$4="Insurance","INS",IF(D$4="BIF","BIF",IF(D$4="Small Business&Agri ","SBA",IF(D$4="Mid/Medium Business","MMB”,IF(D$4="BIL","BIL”,IF(D$4="NIAC","NIA", IF(D$4="Private Banking","PRB",IF(D$4="Network","NET",IF(D$4="Direct","DIR"))))))))))))))

Thanks in advance