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 spreadsheet 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 spreadsheet 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 worksheet. 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 spreadsheet), 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