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!

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

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

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

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

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

I have this code that you gave me, but I don't know how to insert it into my
coding. When I copy and paste it as is, I receive a syntax error. Could you
tell me from start to finish how you would write the coding?:


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


"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

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

dim LastRow as long
dim RngToSort as range

If Intersect(Target, me.Range("R3")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False

with me
lastrow = .cells(.rows.count,"A").end(xlup).row
set rngtosort = .range("a3:s" & lastrow)
with rngtosort
.cells.sort _
Key1:=.columns(7), order1:=xlDescending, _
Key2:=.columns(11), order2:=xlDescending, _
Key3:=.columns(12), order3:=xlDescending, _
header:=xlno
end with
end with
Application.EnableEvents = True
End Sub

But I'm confused. Does row 3 get sorted or kept where it is?

If it gets sorted, then don't change anything.

If it stays put, then change this line:
set rngtosort = .range("a3:s" & lastrow)
to
set rngtosort = .range("a4:s" & lastrow)



Orangepegs wrote:

I have this code that you gave me, but I don't know how to insert it into my
coding. When I copy and paste it as is, I receive a syntax error. Could you
tell me from start to finish how you would write the coding?:

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

"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