Since formulas can't change the values in other cells, what you're
asking isn't possible. Even if they could, you'd have circular
references to deal with.

In addition, you don't really have a two-dimensional array - it's
really three-D since there are two 5x1 areas (which is why you get
the multiple selections error if you try to sort them manually).

Instead of a formula, you could use an event macro to sort when the
data changes. One way:

Put this in the worksheet code module (right-click the worksheet tab
and choose view code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 4 Then sortDandA
End Sub

and put this in a regular code module (insert/module in the VBE):

Public Sub sortDandA()
Dim arr1 As Variant
Dim arr2 As Variant
Dim temp1 As Variant
Dim temp2 As Variant
Dim rng As Range
Dim i As Integer
Dim j As Integer
Dim chg As Boolean

Set rng = Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
arr1 = rng.Value
arr2 = rng.Offset(0, -3).Value
Do
chg = False
For i = 2 To UBound(arr1, 1)
j = i - 1
If arr1(i, 1) arr1(j, 1) Then
temp1 = arr1(i, 1)
temp2 = arr2(i, 1)
arr1(i, 1) = arr1(j, 1)
arr2(i, 1) = arr2(j, 1)
arr1(j, 1) = temp1
arr2(j, 1) = temp2
chg = True
End If
Next i
Loop Until chg = False
Application.EnableEvents = False
rng.Value = arr1
rng.Offset(0, -3).Value = arr2
Application.EnableEvents = True
End Sub

This is fast for relatively small amounts of data. For large
amounts, a faster sort routine should be used.


In article ,
"Randy S" wrote:

I'm looking for a formula that I can enter to do this for
me automatically so I don't have to do Data-Sort
everytime the data changes. I specifically chose Col D to
show the two columns are not adjacent.