How to automatically hide rows based on data validation cell value from different sheet inn the same workbook

Question

Hello,

I'd like to hide a select number of rows on sheet 2(Civil Test) based of a drop down data validation cell value from sheet 1(Pre Con Checklist) and perform this several times within sheet 2(Civil Test) . The cell value from sheet 1 (Pre Con Checklist) is a drop down data validation cell with the only options being Y or N. If Y from cell (O53) on the Pre Con Checklist sheet is selected then I want a range of cells on sheet 2(Civil Test) to show. If N is selected or the cell is left blank, then I want the range of rows from sheet 2 to be hidden. I am able to do this if the Y or N selection is from the same sheet from the code below

***************************************************************

Private Sub Worksheet_Change(ByVal Target As Range)

''this line will Unhide all rows in the sheet

Rows("1:" & Rows.Count).EntireRow.Hidden = False

''''Your conditions as per criteria for cell A3

If Range("A3") = "N" Then

Rows("4:12").EntireRow.Hidden = True

Else

Rows("4:12").EntireRow.Hidden = False

End If

End Sub

**************************************************************

I was able to manipulate the code from the follow post of questions and answers. But I cannot figure out how to pull the Y/N option from a different sheet even though it is in the same workbook, and if the data validation option makes a different? Thank you!

Answer 1

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Worksheets("Other Sheet") ' substitute the real name
        ''this line will Unhide all rows in the sheet
        .Cells.EntireRow.Hidden = False
        ''''Your conditions as per criteria for cell A3
        .Range("A4:A12").EntireRow.Hidden = (Range("A3") <> "Y")
    End With
End Sub

Answer 2

Hi Hans,

Unfortunately this did not work. I updated my question with photos to hopefully get the correct response. Can you please take a look.

Answer 3

Try changing A3 in the code to O53

Answer 4

Hi Hans, The rows are still not hidden with the above code.

Answer 5

Did you change the value of O53?