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!!!

Can you post a sample workbook showing the formulas you have now? Remove confidential info.

Hi NBVC

I have attached a sample database.

For the yellow columns - the user inputs the requirements into worksheet 'Cost Summary' which is then used in formulas in the worksheet 'Evaluation'.

For the green columns - the user inputs requirements into worksheet 'Evaluation' which is then displayed on the worksheet 'Cost Summary'.

The problem is coming when the user wants to sort on 'Cost Summary' by the columns "weighted score" or "priority score" instead of how it is currently sorted by road ID number. is there a way to lock entire rows to one another on different worksheets?

Thanks for your help.

Attached Files

PATH COSTS - SAMPLE.xlsx (29.8 KB, 13 views)

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"))))

The formula I provided simply looks for a match of your ID in the other sheet, and returns the corresponding value from the column referenced in the Match() part.

So if you sort the sheet, the Match() will recalculate based on the ID in the same row.

You will need to do the same for any of the columns that you have that directly link to a a cell of another sheet.

Again, you will need to convert the index columns to text entries as 1.1 and 1.10 are the same thing if left numeric... you just background formatted it to look different.

Hi

Thanks for all your help, yes I have converted the IDs to text.

Can you help me rewrite these two TRUE/FALSE formulas for a match scenario?

=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"))))

Thanks again!

Perhaps these, respectively:

=IFERROR(LOOKUP(INDEX('Cost Summary'!H:H,MATCH(A2,'Cost Summary'!A:A,0)),{0,6,7},{0,-2,-3}),"Select Bridge Length")

=IFERROR(LOOKUP(INDEX('Cost Summary'!J:J,MATCH(A2,'Cost Summary'!A:A,0)),{"Minor","Moderate","Nominal","Su bstantial"},{-1,-2,0,-3}),"Select Earthworks Allowance")

Thanks for all your help.

Those last two formulas did not work. When I cut and pasted it into the cell it returned with the words "Select Earthworks Allowance" and "Select Bridge Length" respectively.

Its looking like with the amount of formulas they are using between the two sheets, it may be easier if they redesigned the workbook so that all the information in held on one datasheet.

Sorry the MATCH() reference was incorrect. It should be referencing Cost Summary!B:B

=IFERROR(LOOKUP(INDEX('Cost Summary'!H:H,MATCH(A2,'Cost Summary'!B:B,0)),{0,6,7},{0,-2,-3}),"Select Bridge Length")

=IFERROR(LOOKUP(INDEX('Cost Summary'!J:J,MATCH(A2,'Cost Summary'!B:B,0)),{"Minor","Moderate","Nominal","Su bstantial"},{-1,-2,0,-3}),"Select Earthworks Allowance")

Please try again

THANK YOU< THANK YOU< THANK YOU.

All seems to be working correctly!

You are welcome.

Not sure if you noticed, but the forum software added a space in the word "Substantial" in the second formula. Please ensure you correct that in your workbook.