Change Update Text Value in column B matching column A

[cspaid]

Column A has a list of repeating items and column B is a Yes/No field. In the example below I want to update Fork from No to Yes and then I want all other copies of Fork to automatically update to Yes. This seems like it should be easy but I am just not seeing the correct solution.

	A	B
1	Widgets	Complete
2	Fork	No
3	Spoon	Yes
4	Knife	No
5	Spoon	Yes
6	Fork	No
7	Fork	No
8	Spoon	Yes
9	Knife	No

<tbody>
</tbody>

 

[Joe4]

Welcome to the Board!

There are a few ways to do this. To update everything "as-is", if all your values in column B and hard-coded and not formulas, then it would probably require VBA.

Otherwise, I would recommend the following structure change.
Create a "master" table that lists east Widget type exactly once, and has the Complete button. So, any changes you make would be to this "master" table.
Then, in your data table above, use VLOOKUP formulas to look up the Complete value for the accompanying widget from your "master" table.
Then any changes you make to the "master" table will automatically be reflected in all the appropriate detailed records.

If you are unfamiliar with VLOOKUP, see the following: https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Change any cell in column B and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Dim val As Range, sAddr As String
    On Error GoTo errHandler
    Set val = Range("A:A").Find(Target.Offset(0, -1).Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not val Is Nothing Then
        sAddr = val.Address
        Do
            val.Offset(0, 1) = Target
            Set val = Range("A:A").Find(What:=Target.Offset(0, -1).Value, after:=val, LookIn:=xlFormulas, lookat:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True)
        Loop While val.Address <> sAddr
        sAddr = ""
    End If
errHandler:
    Application.EnableEvents = True
End Sub

 

[cspaid]

Yes, the two columns are static. Thanks all for your input! I will look into the Master Table idea first but it is good to have the VBA option!