Sorting Columns with formulas

[polaris6]

I think I am at the right place. I have read the posts for the past 3 months and my issue appears similar to those posted here.

I will attach a test sheet which involves the simple sorting of a column of numbers derived from a formula which is contained in the respective cell.

When I select the column (with the header), and select Data>>Sort>>Scores>>descending order, nothing happens; the column remains aligned according to the alphabetical list of the arguments/formulas in the cells.

How can I sort on the column containing the numbers derived from formulas?

I thought I uploaded test3.xls

[JBeaucaire]

Short answer - "can't be done". You formulas have relative references, so as the cells shift during the sort (AND they DO shift, you're wrong when you surmise "nothing happens"), the formulas then reassert because they're relative and then they are reevaluated and the original numbers then move back.

Don't believe me? Click on Tools > Options > Calculation and set it MANUAL. This will keep your sheet from recalculating until you press F9. Now go sort those cells again.

After the sort, the values ARE in descending order, but if you click on the cells themselves, the relative references have reasserted and still show =A10 in cell A2, and since it's now showing a value of 15, that's WRONG.

Anyway, that's why you can't do this this way. You'll have to remove the formulas, or copy the data elsewhere as NOT-formulas, or turn of auto-calc like we did in this exercise.

[polaris6]

Thanks.

I now know I can't sort this way, but how can I do it so that it is automatic? To move the data in the formula reference cells, represents a formula translation, like =A2; to manually insert the number in A2, defeats the purpose of an automated sort.

Here's a practical scenario:

I am the coach of a golf team a the local University. Each of the players on the team must play/practice at least once a week (18 holes of golf). Their individual scores (the sum total of 18 holes) are placed in a cell as the result of a SUM function and a handicap adjustment (in my test example A10:A14). As the coach, I pick the top three (3) players (those with the lowest three total scores for the 18 holes that week) to play in the weekly University competition...hence the sorting function operation. The following week, there is a different set of 18-hole scores and the sorting and subsequest selection is done again. This is process is repeated for the entire season.

At the present time, these totals are done by hand and the selection process is done by observation of the best three total scores. There are twenty (20) players/students involved in this 'game'.

How can I do this so that the sorting and selection process is done 'automatically' with on the entry of the raw scores being required manually?

[polaris6]

You are correct; the numbers are sorted in numerical order when the process is converted to 'Manual'. However, if, under those conditions, I change any of the A10:A14, these changes are not reflected in the A1:A5 displays. Do I now have to go back and change the Manual to Automatic, and then change it back to Manual again for the Sort? Is this the process that must be repeated each time I enter new data?

It seems that there should be a function that not only looks at the absolute content of a reference cell, but also at the value of that which is referenced. This was once referred to as index register referencing, i.e., the contents of the referenced cell(s). This was done way back in the FORTRAN days. Is/has that been lost through software 'evolution'?