I have alist of records containing the name and income number, as follows:
A Name B Income
1 John 9000
2 Mary 8500
3 Smith 10000
I want to sort them in the same sheet but not in the same columns, i want them to be displayed in columns E and F, and when i change the value of field B2 to become 12000, i want it to automatically sorted and refreshed in columns E and F.
If I understand correctly, you want Columns A and B sorted, then Columns E and F to reflect that new sorted order. You can use a worksheet Change macro to accomplish.
In E2, put =A2
and in F2, put = B2
Then, copy E and F down for a few. If you have Excel 2002 or later it will keep adding as you add to A and B.
Right-click on the worksheet tab, and select View Code.
The VBE window will appear. Then paste in this code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim VRange As Range
Set VRange = Range("B2:B" & Range("B65536").End(xlUp))
If Not Intersect(Target, VRange) Is Nothing Then _
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
Adjust to meet your needs.
old, slow, and confused
but at least I'm inconsistent!
thanks for the assistance, and i'm sorry for not responding, i was working outside the office, i tried to modify a value in the excel sheet but i faced this error:
variable not defined