I have constructed a spreadsheet with 4 tabs. Tab 1 is the source document and the cells in Tabs 2-4 are linked to cells in Tab 1. Each tab has 4 banks of weeks as shown in the diagram below.

Due to the different permutations of data that can be input, in order to populate the cells in Tabs 2-4 I have used the IF function. Certain parts of Tab 1 are protected to restrict input and all areas of Tabs 2-4 are protected.

In the diagram below I have added column and row identifiers for ease of reference.



In Tab 1, Column A and Column C are hidden. These columns are used to sort the data by either alpha on surname or Band. Tabs 2-4 also have this information and that too is hidden. The data is not sorted using Tabs 2-4.


I had hoped that, when sorting the data in Tab 1 that all the linked data in Tabs 2-4 would sort in the same way. That is not happening and I assume its due to using the IF function.

Is this a correct assumption?

The question I have then is how to sort Tab 1 by either Surname or Band and for the sort to be correctly reflected in Tabs 2-4?

Grateful for any assistance and indeed advice on how to make this task easier and/or more efficient.