|
Autosort, but keep Row 1 seperate
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! |
Autosort, but keep Row 1 seperate
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 |
Autosort, but keep Row 1 seperate
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 |
Autosort, but keep Row 1 seperate
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 |
Autosort, but keep Row 1 seperate
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 |
Autosort, but keep Row 1 seperate
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 |
Autosort, but keep Row 1 seperate
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 |
Autosort, but keep Row 1 seperate
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 |
Autosort, but keep Row 1 seperate
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 |
Autosort, but keep Row 1 seperate
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 |
Autosort, but keep Row 1 seperate
I wanted it to be an automatic function for when I entered new candidates
into my spreadsheet, 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 sheet (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 sheet changes. I could be asking it to do too many autmatic things, but I'm looking for a really hands-off spreadsheet 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 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 |
Autosort, but keep Row 1 seperate
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 spreadsheet, 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 sheet (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 sheet changes. I could be asking it to do too many autmatic things, but I'm looking for a really hands-off spreadsheet 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 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 -- Dave Peterson |
Autosort, but keep Row 1 seperate
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 spreadsheet, 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 sheet (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 sheet changes. I could be asking it to do too many autmatic things, but I'm looking for a really hands-off spreadsheet 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 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 -- Dave Peterson |
Autosort, but keep Row 1 seperate
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 spreadsheet, 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 sheet (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 sheet changes. I could be asking it to do too many autmatic things, but I'm looking for a really hands-off spreadsheet 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 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 -- Dave Peterson -- Dave Peterson |
Autosort, but keep Row 1 seperate
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 spreadsheet, 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 sheet (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 sheet changes. I could be asking it to do too many autmatic things, but I'm looking for a really hands-off spreadsheet 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 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 -- Dave Peterson -- Dave Peterson |
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 spreadsheet, 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 sheet (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 sheet changes. I could be asking it to do too many autmatic things, but I'm looking for a really hands-off spreadsheet 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 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Autosort, but keep Row 1 seperate
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 |
Autosort, but keep Row 1 seperate
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 |
| All times are GMT +1. The time now is 07:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com