Linking Data from One Workbook to Another and having Cells there stay organized with sorting

Copper Contributor

Hello, I am needing help with keeping cells I input in a workbook that is linked to another workbook staying organized when the original workbook has a filter change.

I have a workbook that I input data into:

A B C D E F

1 2 3 4 5 6

6 5 4 3 2 1

 

I then have another workbook that = the cells in the original workbook:

A B C D E F

1 2 3 4 5 6

6 5 4 3 2 1

 

I want to be able to enter data to the right of these referenced cells in the 2nd workbook but they stay in line with the original rows if the original workbook has a filtering change:

A B C D E F G

1 2 3 4 5 6  1

6 5 4 3 2 1  2

 

The problem is if I filter the original workbook, my column G in the 2nd workbook does not re-arrange with the incoming data from the original workbook and looks like this:

A B C D E F G

6 5 4 3 2 1  1

1 2 3 4 5 6  2

 

Instead of this:

A B C D E F G

6 5 4 3 2 1  2

1 2 3 4 5 6  1


The reason I do this is to be able to have 2 people working without having saving/overlapping issues.

2 Replies
The issue with this setup is you need to use a dynamic array function like SORT which requires the external workbook be open to calculate. Another approach might be to work together with others in the same file and use Sheet View.
https://support.microsoft.com/en-us/office/create-and-manage-sheet-views-in-excel-0eea3dc5-d7d1-44c5...
I do not believe sheet view will be an option for me as it is not saved on one drive. It is saved within a public shared drive on a local network.