Sorting Info pulled from other worksheets

[jenni08]

I have a page on a spreadsheet with 2 columns of data pulled from other
worksheets in the same file - column 1 are scores, column 2 are corresponding
percentages. They are sorted in order by year.

My goal is to set up the page so that all that data is on the page as is it
(ie sorted by year), and then in columns 5 and 6 have the same data, but
sorted asecending by score.

The issue is that the scores and percentages (in 1 and 2) will be constantly
changing when the file is in use, as data is manipulated, etc, and so I can't
just copy and paste value the scores and percentages and sort. I need
columns 5 and 6 to take the data from columns 1 and 2 and sort that data, and
I need to be able to easily update the data in 5 and 6 as it changes in 1 and
2. How can I set it up to do this?

Thank you.

 

[Pete_UK]

In E2 you can enter this formula:

=A2

and this one in F2:

=B2

and copy both down as far as required. Then highlight these two
columns, do CTRL-H and:

Find what: A
Replace with: $A$
click Replace All

CTRL-H again:
Find what: B
Replace with: $B$
click Replace All

Now you can sort columns E and F and because you have absolute
references these won't change when they move. You will have to re-do
the sort, however, whenever the data in columns A and B change, as
this is not dynamic.

As an alternative, you can have an automatic sort by using formulae or
an event macro. The following link is to a thread from earlier today
which gives an approach using a macro:

http://groups.google.com/group/micr...frm/thread/d7b6ff34a0f6fa58/91c874350cc9cd66#

If you do a Google search of the excel groups for sort by formula then
you will find many hits.

Hope this helps.

Pete