How to bind columns - so whole row is updated when sorting ?

[MuffinChef]

Hello

When I sort the data on the Sheet1 from Z to A. the data on the Sheet 2 column C is not updated.

Is there a way to solve this?

I made a sample Excel file & a screenshot.

[TMS]

Because the references in Sheet 2 to Sheet 1 don't change, they just refer to the sorted values. However, the values in column C are static values and completely unrelated to Sheet 1.

Just won't happen.

[MuffinChef]

Because the references in Sheet 2 to Sheet 1 don't change, they just refer to the sorted values. However, the values in column C are static values and completely unrelated to Sheet 1.

Just won't happen.

I understand that. That is why im asking, how to create a relation between Column C & referenced values from Sheet 1 ?

Any ideas on how to do that?

[MrShorty]

Might need more detail in how you are using this. My first thought, if you want to sort and include the values in column C in the sorting, then the values in column C need to be a part of your main data table in sheet1. Then you can link the column C values in sheet2 to column C in sheet1, just like you already have for the columns A and B.

[MuffinChef]

Might need more detail in how you are using this. My first thought, if you want to sort and include the values in column C in the sorting, then the values in column C need to be a part of your main data table in sheet1. Then you can link the column C values in sheet2 to column C in sheet1, just like you already have for the columns A and B.

The simplified example I posted illustrates 100% accurately an issue I am having with a very large collection of tables & multiple sheets.
Hire are some more details, just like you asked.

1) Indeed, I want to sort Sheet1 and include the values in Sheet2 -> ColumnC in the sorting.
2) The values in columnC can not be a part of my main data table in sheet1, it will be unpractical due extremely large table. Data in sheet2-> ColC is updated every day. so if anyone will sort Sheet1 it all goes bad.
3) Data in Sheet2 -> ColumnC is added manually later. But i cant figure out how to connect it so it would change after sorting.

If you have any more questions I would be happy to answer.

[MrShorty]

I don't think those three requirements are readily compatible.

Is it necessary for the sort step to occur on sheet1? The easiest solution that I see (other than adding the data to sheet1) is to add the data to sheet2 and then sort sheet2. Sheet1 remains unchanged. Would that work for you, or must the sort occur on sheet1?

[MuffinChef]

I don't think those three requirements are readily compatible.

Is it necessary for the sort step to occur on sheet1? The easiest solution that I see (other than adding the data to sheet1) is to add the data to sheet2 and then sort sheet2. Sheet1 remains unchanged. Would that work for you, or must the sort occur on sheet1?

Is it necessary for the sort step to occur on sheet1?

Yes

[Richard Buttrey]

Why not do everything in sheet 2 then copy columns A&B to sheet 1, overwriting what's there and sort sheet1?

Incidentally, would you put a meaningful location in your profile. It often helps when we need to consider regional settings, time zones and date formats.

Why indeed do you need a sheet 1 at all since it's just a subset of sheet2.

[MrShorty]

I don't know that what you want is readily acheived with Excel's built in functionality. In order to bind data together for setting, all of the data needs to be in one list. If you cannot add the data to sheet1 before sorting or sort sheets separate from sheet1, then I don't readily see a way to do this across the two tables as you have them now.

Would you be willing to add a third table? I could see doing something where you add your new data to a third table in sheet3 (A and B not linked to sheet1). Then column C of sheet2 can use a lookup function with this table in sheet3 as the source table for the lookup function. Would that be something workable?

[MuffinChef]

I don't know that what you want is readily acheived with Excel's built in functionality. In order to bind data together for setting, all of the data needs to be in one list. If you cannot add the data to sheet1 before sorting or sort sheets separate from sheet1, then I don't readily see a way to do this across the two tables as you have them now.

Would you be willing to add a third table? I could see doing something where you add your new data to a third table in sheet3 (A and B not linked to sheet1). Then column C of sheet2 can use a lookup function with this table in sheet3 as the source table for the lookup function. Would that be something workable?

There are too many tables. It is already hard to navigate, therefore I was looking for more elegant solution rather than adding more tables & making things even more confusing.

Okey maybe an VB script will do the trick ?

The question is now when will the VB script execute?

For example I sort Sheet1, activate Sheet2 (now the vb script should run & store the ColumnC in an array), now the Sheet2 is sorted & now VB script is run again to put ColC values in proper places.

Would that be possible or the VB script runs after all sorting is done?

[Richard Buttrey]

If a VBA macro is permitted then I'd simply use the method I described in post #8

There are then just four lines of code
1. Sort Sheet 2
2. Clear exiisting contents of sheet1
3. Copy columns A&B from Sheet 2 to sheet 1
4. Sort sheet 1

[MuffinChef]

Oh my, well too much thinking results in overthinking.

Solution was to just vlookup to populate the data on the sheet2, this way if you sort Sheet1, Sheet2 stays unaffected.

Problem solved.

Thank you.