Thanks this worked you were a big help I have one more need if you don,t mind.
I have 52 other worksheets cells AR,AS,AT,AV,AW,AX,AZ,BA,BB6:37 & 43:74 in
each worksheet I enter clock # to calculate hours in these cells.
Now on the sheet you & I are working on B column=Names, C column=Clock #, &
D column=hours.
I need to calculate hours in D6:D50 column from other 52 worksheets each
work sheet is named Week 1,Week 2 & so on, any good solutions to do this
"JLatham" wrote:

Mike, I got a little confused here. Let me recap the way I understand things:
In your original lists you have some names in B5:B50 and you have some hour
values associated with them in D5:D50. Presumably we can't change any of
those entries; they are what they are.
Previously we came up with a formula for column E (E5:E50) that would list
the values from D5:D50 in ascending order.
And as I understand it now, instead of the hours from D5:D50 sequenced in
E5:E50, you want the names from B5:B50 to be put into E5:E50 instead of the
ascending hour values.

Hope I got all that right, because that's what I'm about to give you. It's
going to take no less than 4 helper columns for me to do it. I have no doubt
someone else could probably do it with fewer, but not me. This is the best I
could come up with.

I started my helper columns out at column Y.
In Y5 put the formula =D5
in Y6 put the formula =D6+(COUNTIF(D$5:D5,D6)/10)
and fill that down to Y50.
What this does is account for duplicate entries in column D and make them
unique so that the RANK() formula we'll use in a moment doesn't slam us with
a tie value.

In Z5 put this formula =RANK(Y5,Y$5:Y$50,ROW()-4)
and fill it down to Z50

In AA5 enter this formula =SMALL($Y$5:$Y$50,ROW()-4)
and fill it down to AA50

In AB5 we need this formula: =ROW()
and, yes, fill down to AB50

Now we put all of that to use in column E with this formula in E5:
=INDIRECT("B" & VLOOKUP(ROW()-4,Z$5:AB$50,3,FALSE))
and of course fill it down through E50.

You can hide columns Y:AB if you want, and to prevent really wide printouts,
you can use Page Setup to restrict the print area for the sheet to exclude
those columns.

I hope this helps.


"Mike" wrote:

Yes that worked thanks but now what i want to do is to link the D5:D50 column
witch = hours to B5:B50 witch = names of peolple & use the names in column
D5:D50 instead of hours. So some how i have to link the hours to names & have
the names used for smallest to largest your help would be appreciated

"JLatham" wrote:

Try this formula in E5 (which I think you meant instead of the times you
referenced E6).
=SMALL(D$5:D$50,ROW()-4)
and fill it down to E50.

The key is the ROW()-4 part of the formula. At Row 5, it returns 1, at Row
6 it returns 2, etc. See Excel Help for SMALL to understand how that part of
the SMALL function works.


"Mike" wrote:

I need to calculate a range of cells from smallest to largest
example formula will be in column E5:E50 I WANT THE SMALLEST TO LARGEST FROM
column D5:D50 ENTERED IN COLUMN E6:E50 IS THIS POSSIBLE for the sallest #
will be at E6 & the Largest will be at E50