Sort between Excel Sheets with locked cells

**UMOP** · 02-18-2010, 02:20 AM

I need to do a SORT function for Sheet 1, although I need the results to be displayed on Sheet2, as Sheet 1 has locked cells so formulas cannot be deleted.

I cannot use a Macro, as the people at work are using Mac Excel 04 and 08, so at the same stage I cannot use Applescript.

I need to sort from B8 to U150 Keeping all the information in the same rows, but ordering it from highest result to lowest result which is in Column U.

So far I have in Sheet2 for column U8:U150
=Large(Sheet1!$U$8:Sheet1!$U$150,ROWS($U8:$U150))

This displays the values in the correct order, What I would like to know is how I can modify it so that I can display the rest of the information that belongs to that value (Cells B to T) along with the correct result?

If I need something similar in all the other columns can you let me know what the code would be?

If there is an easier way to do a SORT from highest to lowest value and bringing all related information across from another sheet can you also let me know.

Thanks in advance.

**teylyn** · 02-18-2010, 02:36 AM

Hi,

if the values you are sorting are unique, you can use Index/match to find the corresponding neighbouring cells.

=INDEX(Sheet1!B:B,Match($U8,Sheet1!$U$1:$U$150,0))

This formula in B8, copied down and across.

Alternatively, you could just copy the table from Sheet1 to Sheet2 and then sort manually there.

Or, in Sheet2, put this in A1

=Sheet1!A1

copy down and across and use Data - Filter - Autofilter to sort by the column of your choice

hth

**UMOP** · 02-18-2010, 03:13 AM

Originally Posted by teylyn

Hi,

if the values you are sorting are unique, you can use Index/match to find the corresponding neighbouring cells.

Hi Teylyn, thanks for you quick response. I tried your code, it worked, but then I realised I will not have unique values.
So I get the same values in Columns B:T when the result in U is 0.5.
Is there another way?

**teylyn** · 02-18-2010, 03:34 AM

Have you tried the other two ways I listed?

**UMOP** · 02-18-2010, 04:51 AM

Yes, I have tried them.

Although they both work, I would like something that I can have work automatically, as I am not going to be the one who needs to apply the sort to the spreadsheet.

I have approximately 50 people, most of who are not excel savvy, will need to sort their own data. Possibly in about 5 different spreadsheets.
So as you can see, if i was able to automate it so that person x can just select Sheet2, have it already sorted and organised (as per your first suggestion), it would save a lot of dramas for them, me, and anyone else involved.

Human error is what I am trying to avoid.

Any other thoughts on this one?

**UMOP** · 02-18-2010, 05:34 AM

Teylyn,

Potentially solved my problem, but not as elegant as I would have liked.

1. Inserted a ranking formula after my last column of data. (Column X)
=Rank($U8,$U$8:$U$22)+COUNTIF($U$8:U8,U8)-1

This allowed me to get unique ranking numbers from 1 - X values.

2. Changed your code slightly to reflect the ranking of X instead of the U column.

Initial changes appear to be good.

More error checking will go on tomorrow.

Thank you for your input. It was greatly appreciated.

Regards.

Umop.

**UMOP** · 02-19-2010, 01:48 AM

Finer refinements, and it is solved. Thanks for the help

**teylyn** · 02-19-2010, 01:52 AM

Umop, thanks for checking back. care to share how you solved it? For the benefit of people with similar issues that would certainly be great!

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save