Linking a dropdown value to a line

[Montanaja]

Hello,
I have a pretty annoying problem. I have created an Worksheet with two seperate table. In Table 1 there is the raw data and in Table two the data is transferred and each line has a additional column in which a value is chosen from a dropdown menu for each set of data in that line.

Whenever I change the sorting of the data in Table 1 it gets copied in exactly that order. So the data in Table 2 does change. The value chosen from the dropdown list is staying in the line where it was in the first place. Is there a way that I can tell my excel to also switch the value chosen from the dropdown menu whenever the data copied from table one switches the line?

Thank you all in advance!!

Monti

 

[Yongle]

welcome to MrExcel

So this is your problem:
The values in some columns in Table2 are linked by formula to the values in Table1
There is an additional column in Table2 whose values are not linked by formula (values entered manually)
Those cells linked by formula mirror the sorting in Table1
Values in the additional column remain unchanged

Suggestion:
There is no clever "fix"
Consider inserting the data validation in a "new" column in Table1 and selecting the values in Table1
The cells in problem column in Table2 should then be linked by formulato that "new "column in Table1
- everything will then move together

Is there any reason why that is not a practical suggestion ?

 

[Montanaja]

welcome to MrExcel

So this is your problem:
The values in some columns in Table2 are linked by formula to the values in Table1
There is an additional column in Table2 whose values are not linked by formula (values entered manually)
Those cells linked by formula mirror the sorting in Table1
Values in the additional column remain unchanged

Suggestion:
There is no clever "fix"
Consider inserting the data validation in a "new" column in Table1 and selecting the values in Table1
The cells in problem column in Table2 should then be linked by formulato that "new "column in Table1
- everything will then move together

Is there any reason why that is not a practical suggestion ?

Thanks for your response!
The problem that the Table1 is full of sensitive raw data while Table2 is an easy overview. There are different persons working in Table2 and it has to be easy to work with and understandable while in Table1 nobody is allowed to change anything.
Is there really no way to link the value chosen in Table2 to a key value copied from Table1 into Table2 so that whenever the sorting changes, the chosen value in the dropdowncolumn also changes the line accordingly?
Thanks in advance!

 

[Yongle]

You are at a brick wall
- it is not possible to do what you want
- the value in the dropdown column must be derived by formula

Are you prepared to use VBA?
- VBA could mainatain a table of values in a hidden sheet that could be looked up

 

[Yongle]

I should have said that a dropdown would still be used in Table2 but that VBA would write a formula to the cell which would look up the correct value in hidden sheet
When Table1 is sorted the values in problem column would correctly change to match that record