Sorting and transposing values in non-adjacent cells

[Rablet]

Hi,

I have an Excel workbook on Google Drive that has a formula that found the highest value in a row in a series of non-adjacent columns and put it into the cell where the formula is. It looks like this:
=TRANSPOSE(SORT(TRANSPOSE({E5,H5,K5,N5,Q5,T5,W5,Z5,AC5,AF5,AI5,AL5}),1,FALSE))

It was written by someone else and for some reason is no longer working and is simply returning a blank even when there is data in the named cells. It was used to find the highest 6 scores for riders in a series of cycle races as the sum of the highest 6 gave them their overall score for the end of the season.

I have tried downloading the file to my computer to work in Excel 2013 but I don't seem to have the SORT function and when I do the formula above actually disappears from the cell even though other formulas are still there. I've tried thinking of ways to do with rank but I haven't been able to work anything out.

I have uploaded the file so you can see what I am talking about. The formula above would have been in Cell AO5 in the sheet called Automated Results.

Thanks in advance for any help,
Tab

LWCR 2014 Rankings after round 2 (1).xlsx

[Pauleyb]

SORT must have been a macro in the original sheet. In the file you provided, I could not even find the formula you are talking about. What sheet and cell is it in? Second, it is an .xlsx file which cannot contain macros. Maybe your solution is to find the original document and save as a .xlsm (macro enabled spreadsheet).

[Rablet]

Hi,

Thanks for your response.

When I download the file from Google Drive the formula disappears but it was in Cell AO5 on the sheet called Automated Results and then was copied down through all the rows.

From looking at the original formula, I wondered if SORT was a formula that existed in old versions of Excel?

Any further thoughts would be welcome.

Tab