Auto sort dat when updated

  • Thread starter Thread starter bvanderr
  • Start date Start date
B

bvanderr

I am not a novice with Excell, however I have never written a macro. I have
a worksheet that data can be updated on. That data transfers by formula to
another worksheet. I want the second worksheet to auto sort when data is
updated. I believe I need a macro to do this, but I don't know how to even
start.
 
You can sort the data using formulae. There are lots of examples in
the archives if you do a Google search, but if you can't find anything
that suits then you will need to provide more details of what data you
have, how it is organised etc.

Pete
 
Thanks for the response. I guess some more specifics would help. The data I
am working with is on sheet 1. It is named 'salary schedule'. On sheet 2,
named rankings, I have data in columns H and I. Column H is the name of the
data that goes into column I. The data in column I gets updated from sheet
1. It is entered as 'Salary Schedule'!Q31. I want the data sorted
descending by column I, but with column H still next to it so the data's
label (column H), still matches with the data itself.

I hope this isn't confusing and I thank anyone for your help.
 
Even more detail would have been useful, but assume you have the
following names and numbers in columns H and I (I put them in H2:I9):

fred 10
alan 22
jim 15
george 20
mary 18
joan 16
ruth 14
alice 12

Then you can put these formulae elsewhere, eg:

K2: =INDEX(H:H,MATCH(L2,I:I,0))
J2: =SMALL(I$2:I$9,ROW(A1))

and copy down to row 9. It will give you the following:

fred 10
alice 12
ruth 14
jim 15
joan 16
mary 18
george 20
alan 22

i.e. a list sorted by the numbers. If you want it in reverse order
change SMALL to LARGE in the formula in column J. This does not take
account of ties, but hopefully will get you started.

Hope this helps.

Pete
 
Hello,

You can sort via worksheet functions:
http://www.sulprobil.com/html/sorting.html

If somebody offers a "solution" with a RANK() function or SMALL() or
LARGE() function, be suspicious (actually, ignore him/her).
a) It would not work for texts.
b) It would not work for doublettes.

Regards,
Bernd
 
This formula worked awesome! I have rewritten it into several other files!
Thanks for the help.

As for the next post, I will read more about your solution, but this one
worked for what I needed.
Thank-you.
 
Glad to hear that - thanks for feeding back.

As I said, it will not work properly if you have tied values, but
Bernd's solutions can overcome that if it is an issue for you (there
are other ways, still).

Pete
 
Back
Top