Maintain cell value of referenced cell after sorting

[gaudrco]

I have a list of values in range B6:B52; each value represents a report. In column D, I have the name of the reports going down the column referencing column B such that:

D6 = B6
D57 = B7
D108 = B8
D159 = B9
.. and so on

As I add values to column B, the value automatically gets referred to the next cell of the reports in column D. For example, if a type "Apple" in cell B10, then in cell D210 the value will be "Apple".

But if I sort the list of values in column B alphabetically, then all of the values in column D change as well. I need the values in column D to remain where they are after sorting. Somehow, I need to link the cell in column D to the exact cell in column B so that when I sort the list, the values (report names) in column D dont change.

Is there a way to do this?

 

[NoSparks]

There might be an easier way to write that.

For sure.

If Not Intersect(Target, Range("ReportsLOV")) Is Nothing Then
    'do what needs to be done
End If

BUT
Anything added may be dictated by anything else that's already in the Worksheet_Change macro.
Can you post the already existing macro ?

 

[gaudrco]

Private Sub Worksheet_Change(ByVal Target As Range)    Dim strAddress As String
    Dim val
    Dim dtmTime As Date
    Dim Rw As Long
    
    
    If Intersect(Target, Range("CCDSheet")) Is Nothing Then Exit Sub
    
    dtmTime = Now()
    val = Target.Value
    strAddress = Target.Address
    
    Rw = Sheets("Information").Range("B" & Rows.Count).End(xlUp).Row + 1
    With Sheets("Information")
        .Cells(Rw, 2) = "Competitor Comparison Data"
        .Cells(Rw, 3) = strAddress
        .Cells(Rw, 4) = val
        .Cells(Rw, 5) = dtmTime
        .Cells(Rw, 6) = Application.UserName
    End With
    
    '***Every below is what I'm trying to work out in this thread. Incomplete
    With ActiveSheet.ListObjects("ReportsLOV")
If Target.Address = ActiveSheet.Cells(.ListRows.Count + 6, .ListColumns.Count + 1).Select Then
 
     Range("D58").Value = Range(Cells(.ListRows.Count + 5, .ListColumns.Count + 1)).Value
 
End If
End With
End Sub

 

[NoSparks]

Assuming the existing code is fine, try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strAddress As String
    Dim val
    Dim dtmTime As Date
    Dim rw As Long, x As Long

'*********** existing code *********************************************
'
If Intersect(Target, Range("CCDSheet")) Is Nothing Then Exit Sub
    dtmTime = Now()
    val = Target.Value
    strAddress = Target.Address
    rw = Sheets("Information").Range("B" & Rows.Count).End(xlUp).Row + 1
        With Sheets("Information")
            .Cells(rw, 2) = "Competitor Comparison Data"
            .Cells(rw, 3) = strAddress
            .Cells(rw, 4) = val
            .Cells(rw, 5) = dtmTime
            .Cells(rw, 6) = Application.UserName
        End With

'********** code to add report name ************************************
'
'check if the cell is in the B6 and down range
If Not Intersect(Target, Range("ReportsLOV")) Is Nothing Then
'restrict to a single cell only
    If Target.CountLarge = 1 Then
        'check for not blank
        If Target.Value <> "" Then
            'check if already exists
            If Application.CountIf(Range("D:D"), Target.Value) = 0 Then
                'if not existing, put in report name
                For x = 6 To 10000 Step 51
                    If Cells(x, "D").Value = "" Then
                        Cells(x, "D").Value = Target.Value
                        Exit For
                    End If
                Next x
            End If
        End If
    End If
End If

End Sub

 

[gaudrco]

Perfection. Thank you very much!

 

[NoSparks]

You're welcome, glad I could be of assistance.

 

[Peter_SSs]

I'm a bit late on the scene, but could you have done it without vba something like this?

- Have another column nearby your column B values (I've used column A but you may need to insert a new column somewhere) containing sequential numbers pre-populated down as far as you might want.
- Change your column D formulas as shown, Formula in D6 is just copied to D57, D108 etc
- When you want to sort the column B values, include the extra column in the sort.

If any of the column B values are changed, the new values should automatically show up in the corresponding place in column D.

Before the sort:

Excel Workbook
	A	B	C	D
6	1	data 1		data 1
7	2	data 5
8	3	data 8
9	4	data 6
10	5	data 7
11	6	data 9
12	7	data 3
13	8	data 4
14	9	data 2
15	10
16	11
17
57				data 5
58
107
108				data 8
109
157
158
159				data 6
Maintain Reference

After the sort of A6:B16, based on column B:

Excel Workbook
	A	B	C	D
6	1	data 1		data 1
7	9	data 2
8	7	data 3
9	8	data 4
10	2	data 5
11	4	data 6
12	5	data 7
13	3	data 8
14	6	data 9
15	10
16	11
17
57				data 5
58
107
108				data 8
109
157
158
159				data 6
Maintain Reference

Note that if lots of new rows are added at the top, pushing what is now row 6 down to row 51 or below the formulas would return incorrect results. If that is a possibility, it may be possible to deal with.

 

[gaudrco]

Thank you Peter, this is a really great idea and I really like how it allows column B values to be changed and adjusted in column D. I will look into this method if I have any issues with the other solution provided by NoSparks.

 

[Peter_SSs]

Thank you Peter,

You're welcome.