Formula question - does anybody have the answers?
 

What I'm trying to do is simple, but the explanation may not be.

Right now, I have an autosort code in my spreadsheet:

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

The idea is to input data into row 3, and once column R has a value and
"enter" is pushed, the row populates into the rest of the spreadsheet based
on three keys. As is, the spreadsheet works beautifully. The problem: the
value for G3 (mentioned in Key 2) is derived (or should be) from an average
from C3:F3, and when the autosort coding kicks in, all data from row 3 is
inserted into the spreadsheet, and what we're left with is a completely blank
row. If I type in the average formula into that cell, it goes with the rest
of the row into whereever it fits, which is fine, but I need a way to make
that formula always autopopulate into G3. Is there any way to make this
happen, or is there another route I could take?

All help is much appreciated!

Thanks,

Orangepegs

Formula question - does anybody have the answers?
 

Try changing the sort range to not include row 3. You ignored that suggestion
at your other thread.

Orangepegs wrote:

What I'm trying to do is simple, but the explanation may not be.

Right now, I have an autosort code in my spreadsheet:

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

The idea is to input data into row 3, and once column R has a value and
"enter" is pushed, the row populates into the rest of the spreadsheet based
on three keys. As is, the spreadsheet works beautifully. The problem: the
value for G3 (mentioned in Key 2) is derived (or should be) from an average
from C3:F3, and when the autosort coding kicks in, all data from row 3 is
inserted into the spreadsheet, and what we're left with is a completely blank
row. If I type in the average formula into that cell, it goes with the rest
of the row into whereever it fits, which is fine, but I need a way to make
that formula always autopopulate into G3. Is there any way to make this
happen, or is there another route I could take?

All help is much appreciated!

Thanks,

Orangepegs

--

Dave Peterson

Formula question - does anybody have the answers?
 

Rather than inserting a blank row, could you copy-insert a hidden row
which contains the required formulae?

On 16 Jan, 19:08, Dave Peterson wrote:
Try changing the sort range to not include row 3. You ignored that suggestion
at your other thread.



Orangepegs wrote:

What I'm trying to do is simple, but the explanation may not be.

Right now, I have an autosort code in my spreadsheet:

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

The idea is to input data into row 3, and once column R has a value and
"enter" is pushed, the row populates into the rest of the spreadsheet based
on three keys. As is, the spreadsheet works beautifully. The problem: the
value for G3 (mentioned in Key 2) is derived (or should be) from an average
from C3:F3, and when the autosort coding kicks in, all data from row 3 is
inserted into the spreadsheet, and what we're left with is a completely blank
row. If I type in the average formula into that cell, it goes with the rest
of the row into whereever it fits, which is fine, but I need a way to make
that formula always autopopulate into G3. Is there any way to make this
happen, or is there another route I could take?

All help is much appreciated!

Thanks,

Orangepegs--

Dave Peterson

Formula question - does anybody have the answers?
 

how would I do that?

"Rupert" wrote:

Rather than inserting a blank row, could you copy-insert a hidden row
which contains the required formulae?

On 16 Jan, 19:08, Dave Peterson wrote:
Try changing the sort range to not include row 3. You ignored that suggestion
at your other thread.



Orangepegs wrote:

What I'm trying to do is simple, but the explanation may not be.

Right now, I have an autosort code in my spreadsheet:

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

The idea is to input data into row 3, and once column R has a value and
"enter" is pushed, the row populates into the rest of the spreadsheet based
on three keys. As is, the spreadsheet works beautifully. The problem: the
value for G3 (mentioned in Key 2) is derived (or should be) from an average
from C3:F3, and when the autosort coding kicks in, all data from row 3 is
inserted into the spreadsheet, and what we're left with is a completely blank
row. If I type in the average formula into that cell, it goes with the rest
of the row into whereever it fits, which is fine, but I need a way to make
that formula always autopopulate into G3. Is there any way to make this
happen, or is there another route I could take?

All help is much appreciated!

Thanks,

Orangepegs--

Dave Peterson