If your turn the ID entries in both sheets into text entries, so that 1.1 is not the same as 1.10 (when entered as number), then you can use formula in N2:



=INDEX(Evaluation!Q:Q,MATCH(B2,Evaluation!A:A,0))

copied down and in O2

=INDEX(Evaluation!P:P,MATCH(B2,Evaluation!A:A,0))

copied down.

Then you will be able to sort by those 2 columns.

Hi

Thanks for your help.

Can you explain what the new formula is doing and how it is allowing the sort to work correctly?

I have pasted in the two new formulas and copied down, but only about half the values are showing as correct when sorted by either of these two columns.

Is this because of the formulas in the yellow columns "bridge length" and "earthworks allowance" (in the example above) need adjusting too?

ie. if you enter in a value for bridge length or earthworks allowance on 'Cost Summary' the corresponding column in 'Evaluation' performs a True/False function. This provides a value used in the total score calculation.

=IF('Cost Summary'!H2=0,0,IF(AND('Cost Summary'!H2>=1,'Cost Summary'!H2<=6),-2,IF('Cost Summary'!H2>6,-3,Select Bridge Length)))

=IF('Cost Summary'!J2="Nominal",0,IF('Cost Summary'!J2="Minor",-1,IF('Cost Summary'!J2="Moderate",-2,IF('Cost Summary'!J2="Substantial",-3,"Select Earthworks Allowance"))))