How to sort 2 datasheets that have linked formulas

[Rachael]

Hi

I am working with a document someone else has created that has two worksheets in Excel 2016. The document is used to priorities roads requiring new footpaths.

Worksheet 1, named 'Cost Summary', displays the results from calculations performed with the raw data in worksheet 2, named 'Evaluation'. The columns "weighted score" and "priority" display these results.



Each row of both worksheets reflects a specific section of a road and has been assigned a unique ID code. Each row in 'Evaluation' has 2 calculations determining the "weighted score" and "priority" columns displayed in 'Cost Summary'.

Currently both spreadsheets are sorted in order of their ID codes so the section of road on row 2 is the same for both worksheets, therefore the formulas work.

The problem we are having is that 'Cost Summary' requires to be sorted via the "weighted score" and "priority" columns in order to determine budgeting requirements and schedule of works. When this sort is done, the results displayed in these columns become wrong because the section of road now on row 2 of 'Cost Summary' does not align with the section of road on row 2 of 'Evaluation'.

Is there a way to link rows in one worksheet to that of another worksheet so that regardless of sorting the formulas will still be referencing the correct data regardless of the new cell locations?

This is important because this worksheet is ever evolving with new sections needing to be entered and sorted into the existing data.

Thanks for all advice!!!