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]

Please clarify

BEFORE
B10 value is Apple
D210 formula is =B10
D210 displays Apple

SORT column B

AFTER SORT
B10 is Orange
B11 is Apple
Do you want D210 to display Apple ?
(ie B11 value)

 

[gaudrco]

Please clarify

AFTER SORT
B10 is Orange
B11 is Apple
Do you want D210 to display Apple ?
(ie B11 value)

Yes you are correct. In this example, I want Apple to be displayed in D210 forever. Once I add a value the list, the value replicates to the next report cell in column D and forever becomes that value. Maybe you've noticed this pattern; I have a new report cell every 51 rows in column D.

 

[Yongle]

The obvious solution would be to use VBA to copy the value (when a new cell is added ) in B to ( the appropriate cell in ) D
- what should happen if user tries to amend the underlying cell value in B later?

 

[gaudrco]

That is a very good point. If someone changed a value in Column B, I would want the change to happen in Column D to the corresponding cell. I see how that is challenging to maintain if I am sorting the values in column B.

 

[gaudrco]

The reason for this is I have a data validation list of values that refer to the values in column B. If there were a way to sort the data validation list of values cell without affecting the reference cells in column B, then there would be no need to sort column B therefore I would have no issue with my current method. Is there a way to do an isolated sort on the DV list of values?

 

[Yongle]

I am away from my PC so cannot test this but how about reversing your logic.

What happens if you type "Apple" into cell D210 and place the formula in B10
=D210

 

[Yongle]

I am told that latest suggestion won't do the trick

 

[Yongle]

Something that should work
- individual name ranges to hold the values
- named ranges used in formula in both B10 and D210 etc


NamedRangeA with Refers to
="Apple"

NamedRangeB with Refers to
="Orange"

NamedRangeC with Refers to
="Pear"

etc



and the formula (example)
in B10
=NamedRangeA

in D210
=NamedRangeA



Sorting in column B should not affect column D

 

[gaudrco]

That is a really great idea however some of my reports have numbers in the name and you can't use numbers in defined names. I might just have to live without this