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

Yes on sheet 1 (pre con checklist) I have selected both Y and N and not seeing any rows appearing or hiding. I have even tried clearing the data validation and this did not adjust the rows on sheet 2.

Answer 2

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.