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?

 

[Yongle]

Do not give up without a fight!

APPLES & PEARS are not good eamples if APP1ES and P£AR5 can also be present in columnB

1. Post a range of typical values for column B
2. How are you using the value in column D?

 

[gaudrco]

Typical values for column B could include:
- Uppercase/Lowercase
- Spaces
- Special Characters
- Numbers
(Basically could be anything)

My sheet is designed as such:
-Purpose is to capture competitive data
- The column headers are Companies. The headers are not in Row 1.
- Column D is for the report names. Each Report row is grouped in lines of 50 rows (with an additional row separating the reports). The 50 rows become elements that you want to compare across competitors. An example could be "Technology Report". So if I wanted to add a new report, I would enter "Technology Report" at the bottom of the list of column B (cell "B10"). That would automatically name the next open report in column D so the report name "Technology Report" would appear in cell "D210".
- I have a navigation feature that allows someone to choose a Report from a dropdown list of values (refers to list in column B) and click a button to automatically take the user to that report. The logic for the hyperlink is to match the dropdown list cell value with the value in column D.

If I have 50 reports, I need them to be in alphabetical order because searching through 50+ reports in an unorganized list would be a disaster.

 

[Yongle]

What about the content of column D
- could that also anything and everything?

 

[gaudrco]

What about the content of column D
- could that also anything and everything?

Yes

 

[Yongle]

however some of my reports have numbers in the name and you can't use numbers in defined names

I did not fully consider what you said earlier

The name itself cannot contain certain characters etc , but the string containing those characters is not used in the name, it is used under RefersTo

 

[NoSparks]

Could use the Worksheet_Change event to monitor the B6 and down cells.
Write the newly added report name by looking at every 51st row from 6 on and use the first one that's blank.

 

[gaudrco]

Hey NoSparks, that sounds like it would work. I just started to look at the Worksheet.Change event and it seems like it will work. How would you write the code? What do you need to know?

Thanks

 

[gaudrco]

Could use the Worksheet_Change event to monitor the B6 and down cells.
Write the newly added report name by looking at every 51st row from 6 on and use the first one that's blank.

This is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)

 With ActiveSheet.ListObjects("ReportsLOV")

If Target.Address = ActiveSheet.Cells(.ListRows.Count + 2, .ListColumns.Count).Select Then
 
     Range("D6") = Range.ActiveSheet.Cells(.ListRows.Count + 2, .ListColumns.Count).Select
     'Instead of ("D6"), this value should be the next empty cell in column D, skipping every 51 cells, starting at D6.
     End If
End Sub

Something to consider: As soon as a new value is entered to the bottom of the ReportsLOV table, the table is going to expand to include that new value. This might affect how the VBA should look.

 

[NoSparks]

Something to consider: As soon as a new value is entered to the bottom of the ReportsLOV table, the table is going to expand to include that new value. This might affect how the VBA should look.

This is exactly what you want.
The new value becomes part of the table.
Have a look at the existing Worksheet_Change macro in the workbook you previously linked to for how to monitor the range.

 

[gaudrco]

Have a look at the existing Worksheet_Change macro in the workbook you previously linked to for how to monitor the range.

Are you referring to the Worksheet_Change macro I posted above? It is not working right. I pieced it together so I'm sure the logic is horribly wrong. After doing some testing, this piece of code works right; it selects the first empty cell in the "ReportsLOV" table:

With ActiveSheet.ListObjects("ReportsLOV")

ActiveSheet.Cells(.ListRows.Count + 6, .ListColumns.Count + 1).Select
End With

There might be an easier way to write that. The parts I'm missing is how to set up the rest of the code such that when I type a value into the next empty cell in the "ReportsLOV", the value is copied into the next empty cell in column D skipping every 51 lines.