Hi,

In Excel 2003, how can I automatically sort a range of cells which hold the
computation of other cells? Let's visualize this by means of an example.

Let's say I manually input some numbers on cells A10 through D14. These
cells are the source.
The computed cells are A1 through A5 whe
A1=A10+B10+C10+D10
A2=A11+B11+C11+D11
A3=A12+B12+C12+D12
A4=A13+B13+C13+D13
A5=A14+B14+C14+D14

Now, I want the sorting of A1..A5 to automatically happen in real time
according to the computed sums. In other words, every time a cell of the
source changes, A1..A5 should be re-sorted. For instance, if the computed
values are A1=10, A2=3, A3=8, A4=12 and A5=7 then I want them to be
displayed like so:
12
10
8
7
3

Can I bind the sorting of A1..A5 with changes in the source cells A10..D14 ?

Thank you.

For this you are going to have to use a dummy column unless you know how to
code with VBA.

Put the formulas in to a column where they won't be changed, and you can
hide them also.

Say you put the formulas in E1 through E5
In A1 through A5 put in these formulas

A1=Large(E1:E5,1)
A2=Large(E1:E5,2)
A3=Large(E1:E5,3)
etc
etc

Large gives you the ability to pick what max value you want out of an array
of numbers.

So whenever the values change this will change with it

"Fernando Ronci" wrote:

Hi,

In Excel 2003, how can I automatically sort a range of cells which hold the
computation of other cells? Let's visualize this by means of an example.

Let's say I manually input some numbers on cells A10 through D14. These
cells are the source.
The computed cells are A1 through A5 whe
A1=A10+B10+C10+D10
A2=A11+B11+C11+D11
A3=A12+B12+C12+D12
A4=A13+B13+C13+D13
A5=A14+B14+C14+D14

Now, I want the sorting of A1..A5 to automatically happen in real time
according to the computed sums. In other words, every time a cell of the
source changes, A1..A5 should be re-sorted. For instance, if the computed
values are A1=10, A2=3, A3=8, A4=12 and A5=7 then I want them to be
displayed like so:
12
10
8
7
3

Can I bind the sorting of A1..A5 with changes in the source cells A10..D14 ?

Thank you.

If yuo have a lot of data sorting between every entry would slow donw data
entry. Would recommend using a control button to perform the sort after all
data is entered. You would need a macro to do the sort. The macro can be
written to do it automatically or using a control button.

"Fernando Ronci" wrote:

Hi,

In Excel 2003, how can I automatically sort a range of cells which hold the
computation of other cells? Let's visualize this by means of an example.

Let's say I manually input some numbers on cells A10 through D14. These
cells are the source.
The computed cells are A1 through A5 whe
A1=A10+B10+C10+D10
A2=A11+B11+C11+D11
A3=A12+B12+C12+D12
A4=A13+B13+C13+D13
A5=A14+B14+C14+D14

Now, I want the sorting of A1..A5 to automatically happen in real time
according to the computed sums. In other words, every time a cell of the
source changes, A1..A5 should be re-sorted. For instance, if the computed
values are A1=10, A2=3, A3=8, A4=12 and A5=7 then I want them to be
displayed like so:
12
10
8
7
3

Can I bind the sorting of A1..A5 with changes in the source cells A10..D14 ?

Thank you.

Actually, u will neither need macro, nor dummy cells, u may do it with array
formula. Insert in A1:

=LARGE(($A$10:$A$14+$B$10:$B$14+$C$10:$C$14+$D$10: $D$14),row())

press ctrl+shift+enter, drag and copy down. Thanks to akphidelt pointing on
LARGE formula, the rest was quite easy to solve.

"Fernando Ronci" wrote:

Hi,

In Excel 2003, how can I automatically sort a range of cells which hold the
computation of other cells? Let's visualize this by means of an example.

Let's say I manually input some numbers on cells A10 through D14. These
cells are the source.
The computed cells are A1 through A5 whe
A1=A10+B10+C10+D10
A2=A11+B11+C11+D11
A3=A12+B12+C12+D12
A4=A13+B13+C13+D13
A5=A14+B14+C14+D14

Now, I want the sorting of A1..A5 to automatically happen in real time
according to the computed sums. In other words, every time a cell of the
source changes, A1..A5 should be re-sorted. For instance, if the computed
values are A1=10, A2=3, A3=8, A4=12 and A5=7 then I want them to be
displayed like so:
12
10
8
7
3

Can I bind the sorting of A1..A5 with changes in the source cells A10..D14 ?

Thank you.

Even less memory consuming: Highlight A1:A5 and insert my formula in one shot
(press ctrl+shift+enter after inserting).

"Alojz" wrote:

Actually, u will neither need macro, nor dummy cells, u may do it with array
formula. Insert in A1:

=LARGE(($A$10:$A$14+$B$10:$B$14+$C$10:$C$14+$D$10: $D$14),row())

press ctrl+shift+enter, drag and copy down. Thanks to akphidelt pointing on
LARGE formula, the rest was quite easy to solve.

"Fernando Ronci" wrote:

Hi,

In Excel 2003, how can I automatically sort a range of cells which hold the
computation of other cells? Let's visualize this by means of an example.

Let's say I manually input some numbers on cells A10 through D14. These
cells are the source.
The computed cells are A1 through A5 whe
A1=A10+B10+C10+D10
A2=A11+B11+C11+D11
A3=A12+B12+C12+D12
A4=A13+B13+C13+D13
A5=A14+B14+C14+D14

Now, I want the sorting of A1..A5 to automatically happen in real time
according to the computed sums. In other words, every time a cell of the
source changes, A1..A5 should be re-sorted. For instance, if the computed
values are A1=10, A2=3, A3=8, A4=12 and A5=7 then I want them to be
displayed like so:
12
10
8
7
3

Can I bind the sorting of A1..A5 with changes in the source cells A10..D14 ?

Thank you.

Thanks to all who replied.

LARGE is the right function for the job. Works like a charm, but (there's
always a but) what if I want to anchor the sorting of A1:A5 to a neighboring
column, say B1:B5, that hold the labels for the values in A1:A5 ?
In other words, I want B1:B5 to "follow" the real-time sorting of A1:A2. Can
it be done?
I played with Mathematical/Statistical/String functions for a few hours but
couldn't figure out how to create a relation between the two columns (A and
B) so that when LARGE is applied to A1:A5, B1:B5 react accordingly. I'd
rather do it with Excel's built-in functions instead of resorting to VBA.

Thanks again.
Fernando

"Alojz" wrote in message
...
Even less memory consuming: Highlight A1:A5 and insert my formula in one
shot
(press ctrl+shift+enter after inserting).

Hi, am not sure whether without, but definitely possible with auxiliary column.
Array enter in E10:E14 =A10:A14+B10:B14+C10:C14+D10:D14
Insert in F10:F14 ur label.
To see correctly sorted labels in B2:B5, array-enter:
=VLOOKUP(LARGE(($E$10:$E$14),ROW()),$E$10:$F$14,2, 0)
To see labels correctly sorted u do not even need A1:A5, as formula sorting
labels does not use it referring to auxiliary column E. So, if u need to see
just labels sorted, u can delete A1:A5.

"Fernando Ronci" wrote:

Thanks to all who replied.

LARGE is the right function for the job. Works like a charm, but (there's
always a but) what if I want to anchor the sorting of A1:A5 to a neighboring
column, say B1:B5, that hold the labels for the values in A1:A5 ?
In other words, I want B1:B5 to "follow" the real-time sorting of A1:A2. Can
it be done?
I played with Mathematical/Statistical/String functions for a few hours but
couldn't figure out how to create a relation between the two columns (A and
B) so that when LARGE is applied to A1:A5, B1:B5 react accordingly. I'd
rather do it with Excel's built-in functions instead of resorting to VBA.

Thanks again.
Fernando

"Alojz" wrote in message
...
Even less memory consuming: Highlight A1:A5 and insert my formula in one
shot
(press ctrl+shift+enter after inserting).

Thanks!
We're almost there. VLOOKUP(LARGE(($E$10:$E$14),ROW()),$E$10:$F$14,2,0 )
works as long as the numbers in E10:E14 are different. If two or more
numbers in E10:E14 are equal, their corresponding labels shown in B1:B5 are
repeated.
Look at the following three examples (in value/label pairs) to see what I
mean:

EXAMPLE #1:
Input Data:
2, L1
5, L2
8, L3
2, L4
2, L5

This is what the VLOOKUP function mentioned above shows. See that L1 is
repeated three times:
8, L3
5, L2
2, L1
2, L1
2, L1

What I want is this:
8, L3
5, L2
2, L1
2, L4
2, L5


EXAMPLE #2:
Input Data:
2, L1
1, L2
8, L3
2, L4
1, L5

This is what the VLOOKUP function shows. See that L1 and L2 are repeated
twice each:
8, L3
2, L1
2, L1
1, L2
1, L2

What I want is this:
8, L3
2, L1
2, L4
1, L2
1, L5


EXAMPLE #3:
Input Data:
2, L1
2, L2
2, L3
2, L4
2, L5

This is what the VLOOKUP function shows (this is ridicously wrong as L1 is
repeated 5 times):
2, L1
2, L1
2, L1
2, L1
2, L1

What I want is this:
2, L1
2, L2
2, L3
2, L4
2, L5

How do I have to tweak the formula to pick different labels for the same
values ?

Thanks again,
Fernando


"Alojz" wrote in message
...
Hi, am not sure whether without, but definitely possible with auxiliary
column.
Array enter in E10:E14 =A10:A14+B10:B14+C10:C14+D10:D14
Insert in F10:F14 ur label.
To see correctly sorted labels in B2:B5, array-enter:
=VLOOKUP(LARGE(($E$10:$E$14),ROW()),$E$10:$F$14,2, 0)
To see labels correctly sorted u do not even need A1:A5, as formula
sorting
labels does not use it referring to auxiliary column E. So, if u need to
see
just labels sorted, u can delete A1:A5.