Re: Autosort, but keep Row 1 seperate

Check this thread again. There is a solution that sorts the required range--not
the whole column.

Orangepegs wrote:

Okay, here's the code I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("R3")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
Columns("A:S").Sort _
Key1:=Range("B3"), order1:=xlAscending, _
Key2:=Range("G3"), order2:=xlDescending, _
Key3:=Range("K3"), order3:=xlDescending, _
header:=xlYes

Range("A3:S3").Insert Shift:=xlDown
Application.EnableEvents = True
End Sub

Key 1 sorts the list based on an alpha value (A, N, and U)
Key 2 sorts the list based on a numeric value (this is the line where I need
the average formula for C3:F3)
Key 3 sorts the list based on a date
The R3 function is where I confirm all data in the line. When I enter any
value, the sort kicks in.

"Dave Peterson" wrote:

Actually, I don't understand why just sorting rows 4 to whatever won't work.

Maybe you should post the code that you're using.

Orangepegs wrote:

I guess I don't really follow. The way it's set up, lines 1 and 2 are
headers, line 3 is where the data is input, and it's sorted into lines 4
through whatever. It is sorted by code, and it works wonderfully thanks to
your previous tips. The only problem I have right now is being able to have a
formula automatically populate cell G3, as it
always disappears in the sort.

You seem to have a pretty good idea of what I'm looking to do. In this case,
how would you set everything up? And please be specific, as my knowledge of
Excel jargon is limited. I've had people to "just do this" or "just do that,"
and unless the steps of doing those things are broken down for me, I'm
completely lost.

Again though, I really appreciate your help and patience in this matter.

Thanks,

Orangepegs

"Dave Peterson" wrote:

If you're sorting by code, the change the code to start the sort range in row 3.

If you're sorting manually, don't include row 3 in your sort range.



Orangepegs wrote:

I wanted it to be an automatic function for when I entered new candidates
into my spread***, and I wanted to base it off of several cells, rather
than just one. No matter what I do, when row three is sorted into the rest of
the document, the replacement row comes in completely blank. If there is a
better way to do this, I am open to suggestions. As is, I have 16 different
sheets within the book, and each has a top 5 group that is linked to the
first *** (so it summarizes the top five candidates of each skill-set). I
used an "indirect" formula for that, so it continuously updates when the
order of any *** changes. I could be asking it to do too many autmatic
things, but I'm looking for a really hands-off spread*** here...

"Dave Peterson" wrote:

Why not just sort the range with the data? There was a suggestion a few posts
ago that sorted the range starting with row 3.

Orangepegs wrote:

I think you're right. The formula I'm using is in a cell that is sorted. Is
there a way to have a cell default to a certain formula? Do I need to create
a macro button or something?

Thanks,

"Dave Peterson" wrote:

I still don't think that it's the formula that's causing the problem. My guess
is that you're including that row in the range to sort (and it's not the header
row).



Orangepegs wrote:

It could be because I'm putting the code directly into a cell rather than
putting it in the sheets coding with everything else related to the autosort.
Problem is, I don't really know how to utilize that aspect of Excel, and I
don't know where to input my average code, or even if that code changes when
putting it in there rather than the actual cell. Regardless, my code doesn't
actually disappear. Instead, it sorts into the row that the rest of the
information moves to.

Oh, and just so you know, I moved my input row to 3 because the formatting
of my input row changes according to the row just above it (so the formatting
of my header was affecting the coding of line 2). So I just changed the input
line to 3, left 2 blank, and reduced it so it's not visible. That seemed to
have taken care of the problem. Anyhow, is there something I can do about
this formula, or am I going about this the wrong way?

Thanks,

"Dave Peterson" wrote:

I would think that sorting rows 3 to whatever would mean that whatever is in row
2 will never change.

I guess I don't understand why it does for you--did you try the code changes?

Orangepegs wrote:

I think we're close to what I'm trying to accomplish... I am a recruiter, and
I'm trying to create a spread*** for my candidates to categorize them based
on skillset, qualifications, and rankings. The overall rankings is where the
average formula comes into play. This is similar to what my spread*** looks
like (The three determining factors on this, however, would be columns B and
G (for simplicity's sake I reduced and rearranged columns for this example. I
want it to sort by Status first, then Overall):

A B C D E
F G
1- Name: Status: Attitude: Comm: Apprnc: Exprience:
Overall: (etc)
2- Jon A 10 4 7
10
3- Mary A 9 9 8
6 8
4- Kim A 7 1 1
1 2.5
5- Paul U 2 1 4
4 2.8

As my coding states, the header (which is frozen) is in Row 1, and the input
line is Row 2. On Column S, when I enter in any letter and press enter, it
inserts everything in Row 2 to the appropriate row within the work***. The
overall score is one of the sorting factors, but I want it to be based off of
an average from columns C through F. Is there a place in the coding I can
input a code to be sure that G2 is averaging C2:F2?

I might be approaching this from the wrong angle, but I'm new to the page
coding thing, and all I really know are basic formulas.

Thanks,

Orangepegs

"Dave Peterson" wrote:

It sounds like your headers are in row 1, you have the =average() formulas in
row 2 and the data in rows 3:xxxx.

You could sort rows 3 to xxxx and turn headers to off.

I like to do something like:

dim LastRow as long
dim RngToSort as range
with worksheets("sheet9999")
lastrow = .cells(.rows.count,"A").end(xlup).row
set rngtosort = .range("a3:s" & lastrow)
with rngtosort
Key1:=.columns(7), order1:=xlDescending, _
Key2:=.columns(11), order2:=xlDescending, _
Key3:=.columns(12), order3:=xlDescending, _
header:=xlno
end with
end with

===
PS. Another thing you may want to consider. Put your average formulas in Row
1. Put the headers in Row 2.

Then select A3 and window|Freeze panes.

Then the averages will always be visible above the headers. And the averages
look like they're not part of the data.

PPS. And you may want to consider using =subtotal(1,...) as your average
formulas. If you apply data|filter|autofilter, then this function will ignore
the hidden rows. Kind of neat if you're looking at only certain categories.

Orangepegs wrote:

That was very helpful, thank you. It solved the header issue. However, there
is one more issue at hand. The cell G2 is meant to average a2:f2. Whenever
the autosort kicks in, it leaves G2 as a blank cell with no more formula. Is
there a way to correct that?

Thanks,

Orangepegs

"Dave Peterson" wrote:

I'm not sure what you're doing with "Key1:=Range("G2,k2,l2")", but if you really
meant to sort by 3 key fields, you'll want to use Key1, key2, key3.

And one of the options when you sort is to allow excel to guess at the header
row. Since you know your data has headers, just tell excel that in your code:

Columns("A:S").Sort _
Key1:=Range("G2"), order1:=xlDescending, _
Key2:=Range("k2"), order2:=xlDescending, _
Key3:=Range("l2"), order3:=xlDescending, _
header:=xlyes



Orangepegs wrote:

I've figured out how to autosort my columns, but there are two problems: I
can't seem to do it without messing up my frozen window pane in Row 1 (this
describes the information in the spread***), and I want an "average"
formula to populate one of the cells I use in the autosort formula (It
disappears every time the autosort function kicks in).

Anyhow, here's the formula I came up with. The main problem is with the
first row being included in the sort. I thought I had it covered, but
obviously it doesn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("S2")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
Columns("A:S").Sort Key1:=Range("G2,k2,l2"), order1:=xlDescending
Range("A2:S2").Insert Shift:=xlDown
Application.EnableEvents = True
End Sub

Any and all help is much appreciated!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
.