Hi
I have a data sheet which contains in monthly groups of columns,
sales staff volume, gross profit, average gross
- one row per salesperson, three columns (vol,gross,ave) per month

I want to produce a report (which could be on the same sheet, within month
group),
which separates volume, gross profit and average, all sorted by rank for
that period.
e.g. for one month, data is:
A B C D
spsn1 27 $2,700 $100
spsn2 15 $3,000 $200
spsn3 20 $3,900 $195

needs to sort / print as:
------------------------------
ladder by volume
spsn1 27
spsn3 20
spsn2 15

ladder by gross
spsn2 $3,000
spsn3 $3,900
spsn1 $2,700

ladder by average
spsn2 $200
spsn3 $195
spsn1 $100
--------------------------------

I can't even get a start - fixed formula on sheet to dynamically update the
'print' area when entries made by month, or macro to run to generate it, or
new sheet for report, or or or ????
Starting point / help greatly appreciated
Thanks, Ian

Here's one play which delivers the 3 auto-descending sorted reports
(by volume, by gross & by average) via non-array formulas

Sample construct available at:
http://www.savefile.com/files/1510963
Auto-Descending Sort by Vol Gross n Av.xls

Assume source data in cols A to D, data from row2 down

Create 3 arb tie-breaker criteria cols
(for volume, gross & average)
-----------------
Put inE2: =IF(B2="","",B2-ROW()/10^10)
Copy E2 to G2, fill down to say, G10
to cover the max expected data extent
(leave E1:G1 empty)

Create ladder by volume
------------------
Put in I2:
=IF(ISERROR(LARGE($E:$E,ROW(A1))),"",
INDEX(A:A,MATCH(LARGE($E:$E,ROW(A1)),$E:$E,0)))
Copy I2 to J2, fill down to J10
(cover the same fill extent)

Create ladder by gross
---------------
Put in L2:
=IF(ISERROR(LARGE($F:$F,ROW(A1))),"",
INDEX(A:A,MATCH(LARGE($F:$F,ROW(A1)),$F:$F,0)))

Put in M2:
=IF(ISERROR(LARGE($F:$F,ROW(A1))),"",
INDEX(C:C,MATCH(LARGE($F:$F,ROW(A1)),$F:$F,0)))
Select L2:M2, fill down to M10
(cover the same fill extent)

Create ladder by average
--------------
Put in O2:
=IF(ISERROR(LARGE($G:$G,ROW(A1))),"",
INDEX(A:A,MATCH(LARGE($G:$G,ROW(A1)),$G:$G,0)))

Put in P2:
=IF(ISERROR(LARGE($G:$G,ROW(A1))),"",
INDEX(D:D,MATCH(LARGE($G:$G,ROW(A1)),$G:$G,0)))
Select O2:P2, fill down to P10
(cover the same fill extent)

The 3 ladders will auto-update based on the source data cols A to D. Ties
in the values (volume, gross or average) if any, will be reflected in the
same relative order that the tied lines appear within the source data.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ian" wrote in message
u...
Hi
I have a data sheet which contains in monthly groups of columns,
sales staff volume, gross profit, average gross
- one row per salesperson, three columns (vol,gross,ave) per month

I want to produce a report (which could be on the same sheet, within month
group),
which separates volume, gross profit and average, all sorted by rank for
that period.
e.g. for one month, data is:
A B C D
spsn1 27 $2,700 $100
spsn2 15 $3,000 $200
spsn3 20 $3,900 $195

needs to sort / print as:
------------------------------
ladder by volume
spsn1 27
spsn3 20
spsn2 15

ladder by gross
spsn2 $3,000
spsn3 $3,900
spsn1 $2,700

ladder by average
spsn2 $200
spsn3 $195
spsn1 $100
--------------------------------

I can't even get a start - fixed formula on sheet to dynamically update
the
'print' area when entries made by month, or macro to run to generate it,
or
new sheet for report, or or or ????
Starting point / help greatly appreciated
Thanks, Ian