smallest to largest

[Mike]

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 D550 ENTERED IN COLUMN E6:E50 IS THIS POSSIBLE for the sallest #
will be at E6 & the Largest will be at E50

 

[JLatham]

Try this formula in E5 (which I think you meant instead of the times you
referenced E6).
=SMALL(D$5$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]

Yes that worked thanks but now what i want to do is to link the D550 column
witch = hours to B5:B50 witch = names of peolple & use the names in column
D550 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

 

[Bernd P]

Hello Mike,

Two possible ways:
http://sulprobil.com/html/sorting.html
http://sulprobil.com/html/sort_vba.html

Regards,
Bernd

 

[JLatham]

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 D550. 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 D550 in ascending order.
And as I understand it now, instead of the hours from D550 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$55,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]

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 D650 column from other 52 worksheets each
work sheet is named Week 1,Week 2 & so on, any good solutions to do this

 

[JLatham]

Would it be possible for you to send me a sample of this workbook along with
examples of what you're expecting? Help From at JLatham Site dot Com
without any spaces will get email to me.

In the meantime, look in Excel Help for the subject
'Refer to the same cell or range on multiple sheets'
If all of your sheets are laid out exactly the same, including the order of
names on them, then this may be of help to you. You can 'burrow' through a
stack of sheets, performing some functions on the same cell/range in multiple
sheets. The sheets have to be contiguous, and the formula calculation
includes the first and last sheet (the two referenced in the formula) along
with all sheets in between them.

Example =SUM(Sheet1:Sheet3!A5)
gives you the total of the values in cell A5 on Sheet1 and Sheet3 and all
sheets in between them.

 

[Mike]

I will try to simlify this, The sheet were working on column B= names, column
C=clock #,column D=hours, Now Week 1:Week 52 im looking for clock # to match
the ones on the sheet were working on C column, so i can calculate hours some
how in same formula, Now the cells we are looking for to match for C column
from each week are AR,AS,AT,AV,AW,AX,AZ,BA,BB-6:37 & 432:74 & 80:111 &
117:148 & 154:185 & 191:222 & 228:259 I HOPE THIS WILL HELP BECAUSE IM
CONFUSED

 

[JLatham]

Son on the WEEK sheets we are involved with columns
C - which contains a clock# to match from the summary sheet we have been
working with?
Then we're dealing with columns AR through AX (AR:AX), and AZ through BB
(AZ:BB), and within those columns we have 7 groups of 31 rows? [What happened
to column AY?]
Is that correct?
This almost looks like a kind of calendar setup. But since you say their
names are 'Week 1' through 'Week 52' (52 separate sheets), then perhaps not.

Can you explain to me what the columns/rows on the Week # sheets represent?
I begin to think the 7 groups of 31 rows each represent a day of the week,
but why 31 rows instead of 24?

Are the clock #s in column C of the first sheet we worked on all unique?
That is, each name is associated with a unique clock #?

And finally, boy, some real-world data from this workbook would sure help
make sure that the solution actually is a solution!

 

[JLatham]

Mike,
Let's put it this way: it would be much easier at this point if you'd at
least initiate email contact with me so that questions and answers can go
faster. I can easily locate a Clock # from the sheet we've been working with
in any other sheet in the workbook. The question now becomes what do I do
when I've found it. You listed 9 columns on the 'Week #' sheets along with
column C (containing clock #'s to be found) -- what do I do with the data in
those columns?

Again the email (Remove Spaces) HelpFrom @ jlatham site. com