Cell Status Change In Sheet1 Dependant Upon Lookup Value, and Reflects In Sheet2 - VBA May Be Required

[Saviene]

System: Windows 7 Professional 64-bit
Software: Excel 2013

Greetings Everyone!

I have been trying to manipulate snippets of code here and there to find a solution to this project. I wouldn't say that I'm a VBA novice, as I am not actively trying to code, rather than see what works from other's suggestions/solutions and tweak as I go. This worked out wonderfully for creating a userform, and having it work as I need it to. Unfortunately, I have not found a working solution to the following issue.

So in Cover (Sheet1), I have my data nicely laid out and taken from Checklist (Sheet2). Everything is working great, except when I need to code a cell change. My objective is to display the current status (Cover!D5:D14) of requirements from Checklist which has various rows and columns. Please note that there are 10 requirements in the checklist, but not all of them will be used.

In Cover!D5:D14, there needs to be either "Yes," "No," or blank. This data will be pulled from Checklist, but the data of Cover!C1 needs to match the corresponding column of Checklist. The cell that "Yes" and "No" will originate/change from will be a drop down list in cells Cover!E5:E14. When one of these cells changes, it needs to update the according requirement in Checklist. This, in turn, will update what is shown in current status (Cover!D5:D14).

The even trickier part is that when I change the drop down in cell Cover!C1, the cells in Cover!E5:E14 must clear to reflect neither "Yes" nor "No," but does not effect the current status (Cover!D5:D14) of the inquiry of Cover!C1 when this happens. I do have a blank cell in my data validation list for the Yes/No drop down.

I have tried dependent drop downs, IF/THEN statements, INDEX/MATCH statements, macros, and various codes. I have tried using check boxes and radio buttons in VBA, as well as form controls. I cannot figure out how to make this happen. I apologize that at this time, I do not have any code to offer for a starting point. Nothing has been close to working for this.

I tried to post an image as the FAQ thread suggested, but it wasn't working out too well. Please let me know if these links do not work for the images, I will try something else.

Any help would be greatly appreciated. Thank you!

Cover (Sheet1)
https://i.imgur.com/CFp8EbZ.png

Checklist (Sheet2)
https://i.imgur.com/3BBYVkP.png

 

[Saviene]

Here is the code that I currently have in place, and it is not functioning. There is no macro tied to the code, if that's what's needed.

Private Sub Workbook_Open()


End Sub
Private Sub Worksheet_Change(ByVal target As Range)
    Dim cells_to_watch As Range
    
    ' these are the cells that will trigger the below code when modified
    Set cells_to_watch = Range("B1")
    
    ' if the cell which was modified (target) is in the range we are watching, run the code.
    If Not Application.Intersect(cells_to_watch, Range(target.Address)) Is Nothing Then
        ' Find the column of the record we are looking for in the checklist
        Dim col As Integer: col = 2
        While ActiveWorkbook.Sheets("Checklist").Cells(2, col).Value <> target.Value
            If ActiveWorkbook.Sheets("Checklist").Cells(2, col).Value = "" Then
                MsgBox "ERROR: This lease was not found in the records."
                Exit Sub
            End If
            
            col = col + 1
        Wend
        
        Dim main As Worksheet: Set main = ActiveWorkbook.Sheets("Cover")
        Dim dataset As Worksheet: Set dataset = ActiveWorkbook.Sheets("Checklist")
        
        ' set the requirements
        main.Cells(5, 3).Value = dataset.Cells(9, col).Value
        main.Cells(6, 3).Value = dataset.Cells(11, col).Value
        main.Cells(7, 3).Value = dataset.Cells(13, col).Value
        main.Cells(8, 3).Value = dataset.Cells(15, col).Value
        main.Cells(9, 3).Value = dataset.Cells(17, col).Value
        main.Cells(10, 3).Value = dataset.Cells(19, col).Value
        main.Cells(11, 3).Value = dataset.Cells(21, col).Value
        main.Cells(12, 3).Value = dataset.Cells(23, col).Value
        main.Cells(13, 3).Value = dataset.Cells(25, col).Value
        main.Cells(14, 3).Value = dataset.Cells(27, col).Value
        
        ' set whether they have the requirements
        main.Cells(5, 5).Value = dataset.Cells(10, col).Value
        main.Cells(6, 5).Value = dataset.Cells(12, col).Value
        main.Cells(7, 5).Value = dataset.Cells(14, col).Value
        main.Cells(8, 5).Value = dataset.Cells(16, col).Value
        main.Cells(9, 5).Value = dataset.Cells(18, col).Value
        main.Cells(10, 5).Value = dataset.Cells(20, col).Value
        main.Cells(11, 5).Value = dataset.Cells(22, col).Value
        main.Cells(12, 5).Value = dataset.Cells(24, col).Value
        main.Cells(13, 5).Value = dataset.Cells(26, col).Value
        main.Cells(14, 5).Value = dataset.Cells(28, col).Value
    End If
End Sub

Thank you for any help/suggestions!

 

[Saviene]

bump

 

[Kenneth Hobson]

Cross-posted: https://www.excelguru.ca/forums/sho...nto-sheet2-then-reflect-that-change-on-sheet1