[Zack Barresse]

So if the A value is a GPB type, should it only look at the GPB References? What if there is a reference to both GPB and PQS References? How should it handle if there are two references there, take any action on the other one? For example row 15...

Col A: PAT0001
Col B: PQS
Col C: V7111 V7101 V7106
Col D: PAT1000 PAT1001 PAT1002

I know that PAT0001 is a PQS reference, so my question is, will we only then be looking at those col C values down column A and appending those references in col C (PAT0001)? And if not, what do you want to do about the references to GPB (col D)?

[ab11viva]

The B value is only "in play" when the corresponding A value is placed as a reference to another A value. If there is a reference to both GPB and PQS documents, that is fine, as the relationship that will be created will go to the rows where those references are in col A and add the original col A value to the appropriate col C or D cell. (based on the type (col B) of the original col A value) Let's look at your example from row 15. (I corrected the B value as this should be a GPB)
Col A: PAT0001
Col B: GPB
Col C: V7111 V7101 V7106
Col D: PAT1000 PAT1001 PAT1002

In this case, we need to create the relationship back to PAT0001 from all corresponding col C and D values. So, we need to locate the rows where V711, V7101, PAT1000, etc are all listed in col A. (6 different values) In these rows, we need to add PAT0001 to col D for each value since PAT0001 is a GPB - this is where the col B value is used. (if it were a PQS (i.e. B15 = PQS), then we would add PAT0001 to col C for all referenced values)

In short, all referenced values (col C and D) need to be linked/referenced back to the original col A value. The B value is used to direct where these references should be made on the rows where the original C and D values are listed as A values.

That sounded simpler before I wrote it down...

[Zack Barresse]

Ok, I think I understand where you're going with this. But in your example here, if PAT0001 was a GPB reference, wouldn't you want to lookup only PAT1000 PAT1001 PAT1002 values in column A and append PAT001 to column D? You mentioned V7111 and V7101 as well, which are references to PQS. Is that correct?

[ab11viva]

For the PAT0001 example, the col B value only determines where the reference to PAT0001 should be placed - not the references for this document.

In other words, the type of a col A value does not impact what other documents can be referenced from it since any col A value can reference both GPBs and PQSs. The col B value is used when placing it's corresponding col A value into either the C or D column for all referenced documents. In the example this would mean that all referenced values (PAT1000, PAT1001, PAT1002 - all from col C, and V7111, V7101, V7106 - all from col D) should contain a GPB reference to PAT0001 (placed in col D of all 6 A values listed above).

[Zack Barresse]

Okay, gotcha. Try this code out. Be sure to SAVE your work FIRST. Please, please, please, save your work. This code goes into a standard module and needs to be called by another [event] procedure (will post it below). It works in my testing, but please be sure (hence saving, or even make another copy of your workbook first) as with code there is no undo and I'm not into destroying data that is unrecoverable. If you save FIRST, you can always close without saving and then re-open.

In your VBE, insert a new Module and paste this code in there...

Option Explicit

Sub CheckForReferences(rCell As Range)
    
    Dim ws As Worksheet, i As Long
    Dim rLook As Range, rFind As Range, rRef As Range
    Dim aVals() As String, sType As String
    Dim sTemp As String
    
    Set ws = rCell.Parent
    
    '## Should either be GPB or PQS
    sType = rCell.Offset(0, 1).Value
    
    sTemp = Trim(Trim(rCell.Offset(0, 3).Value) & " " & Trim(rCell.Offset(0, 2).Value))
    aVals = Split(sTemp, " ")
    Set rLook = ws.Columns(1)
    
    If UBound(aVals) < 0 Then Exit Sub
    
    Call TOGGLEEVENTS(False)
    
    For i = LBound(aVals) To UBound(aVals)
        Set rFind = rLook.Find(What:=aVals(i), LookAt:=xlWhole, MatchCase:=True)
        If Not rFind Is Nothing Then
            Select Case sType
            Case "GPB"
                Set rRef = rFind.Offset(0, 3)
            Case "PQS"
                Set rRef = rFind.Offset(0, 2)
            End Select
            If Len(rRef.Value) <> 0 Then
                If InStr(1, rRef.Value, aVals(i)) = 0 Then
                    rRef.Value = rRef.Value & " " & rCell.Value
                End If
            Else
                rRef.Value = rCell.Value
            End If
        End If
    Next i
    
    Call TOGGLEEVENTS(True)
    
End Sub

Public Sub TOGGLEEVENTS(blnState As Boolean)
'Originally written by Zack Barresse
    With Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
    End With
End Sub

In your worksheet, right click the sheet tab and select View Code. Paste this change event code in there...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    Call CheckForReferences(Target)
End Sub

Let us know how it works. It seems to follow your guidelines on my testing.

[ab11viva]

Okay, brace yourself for a dumb question...

What do I need to do to get this to run? I created a new module and added the change event code but it doesn't appear that anything happened.

Do I need to turn this on or something???

Sorry...

[Zack Barresse]

Well it runs automatically, when you change any cell in column A of that sheet. Isn't that what you wanted? If not please let us know exactly how you wanted to run this, maybe I got the wrong idea..

[ab11viva]

okay - I see what you did. This is sort of how I need it to run except for I'm assuming that there will not be updates to the col A value but rather to the col C and col D values.

So, when you change a value in col C or col D, that corresponding A value is then put into the correct col C or D value in another row.

Basically, I want it to work the way you set it up except that I need the updates to col C and D to also be considered.

does that make sense?

Thanks again for all your help.

[Zack Barresse]

Okay, so the change to trigger the code to run is col C or D? If that is the case, then change the worksheet event code (the lower of the two code snippets) to this...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 3 Or Target.Column <> 4 Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    Call CheckForReferences(Me.Cells(Target.Row, 1))
End Sub

Let us know if that works the way you want.

If you wanted to go through all of these at one time, we could write a one-time-only macro that would use the first sub routine and just loop through all cells calling it. That's if you want to currently do everything you already have by the 'rules' of the routine. It would save you from going down each cell in col C (or D) and hitting F2, Enter, F2, Enter, etc. Not sure if that is anything you're looking for, but it can be done via code fairly easily too.

HTH

[ab11viva]

Zack,

I updated the event code but it doesn't appear to be triggering anything. I added a value in a col C and D cell and nothing happened. I have attached the sheet with the code - did I do something wrong?

As for the one-time macro that you mentioned, that would definitely be helpful.

Thanks
Adam

[Zack Barresse]

Right-o, bad logic on my part, apologies. Use this instead...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column = 3 Or Target.Column = 4 Then
        Call CheckForReferences(Me.Cells(Target.Row, 1))
    End If
End Sub

[ab11viva]

Much Better. I think this is exactly what I need it to do but there does appear to be some odd side effects.

When I enter a value into a reference cell (col C or D), the correct relationship is set but if I access that cell again and click enter or enter an additional reference, it seems to duplicate the entries.

For a test, use row 68 (SL1006) which should have a D68 value of SL1005. (which is the row right above it - 67) Access this cell (D68) and click enter. You'll see that D67 is properly populated with SL1006. Now access D68 again and click enter - another entry is made in D67. If I then attempt to delete one of the entries in D67, a get a duplicate in D68 and so on... Is it possible to remove the possibility of duplicates?

Thanks

[Zack Barresse]

Okay, I think I miss-typed a line of code, because I coded around that. If I understand this right, just take this line of code...

If InStr(1, rRef.Value, aVals(i)) = 0 Then

... and REPLACE it with this line of code ...

If InStr(1, rCell.Value, aVals(i)) = 0 Then

That should look at the right data. Let us know how that works.

[ab11viva]

I updated the code and I still see this issue... Doesn't appear to have changed anything - at least not that I can tell.

So close...

[Zack Barresse]

Oh, I had the InStr() setup backwards, sorry. That line I just referenced in post #28, it should in fact be this...

If InStr(1, rRef.Value, rCell.Value, vbTextCompare) = 0 Then