Formula Help

[Guest]

I have several columns that I want to sort on a separate worksheet. I input
the info on sheet 1 and would like for specific columns only to appear on
sheet 2.
For example:
Column A Column B Column C Column D
(area) (check/recheck) (hours) (problems)
South CH 1 0
West RECH 1 1
South CH 1 1
West CH 1 1
West RECH 2 0

On Sheet 2 I would like to sort The area and only have Column A and the
corresponding Column D appear. That way whenever I input the info on sheet 1
it automatically sorts the info and puts it into order by West, South etc. on
sheet 2.
How do I do this?

 

[Sandy Mann]

What do you mean by "sort"? Do you want Sheet 2 to look like:

Column A Column B
(area) (problems)
South 0
South 1
West 1
West 1
West 0


or
Column A Column B
(area) (problems)

South 1
West 2

Or what?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

I would like it to look like:

Column A Column B Col C Col D
(area) (problems) (area) (problems)
South 1 West 1
South 0 West 1
West 0

Thanks again.

 

[Sandy Mann]

What you want may well be able to be done with formulas and someone may come
back with a formula solution but is not try entering this in the Worksheet
module of sheet 1 (with sheet 1 the active sheet, right-click on the sheet
tab and select View code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Intersect(Target, Range("D")) Is Nothing Then Exit Sub

Dim LastRow As Long

Sheets("Sheet2").Columns("A").ClearContents

Application.ScreenUpdating = False

With Sheets("Sheet1")
'Replace "Sheet1" & "Sheet2" witj your sheet names
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
.Columns("B:C").EntireColumn.Hidden = True

.Range(Cells(1, 1), Cells(LastRow, 4)) _
.AutoFilter Field:=1, _
Criteria1:="=South"

.Range(Cells(1, 1), Cells(LastRow, 4)) _
.Copy Destination:= _
Sheets("Sheet2").Range("A1")

.Range(Cells(1, 1), Cells(LastRow, 4)) _
.AutoFilter Field:=1, Criteria1:="West"

.Range(Cells(1, 1), Cells(LastRow, 4)) _
.Copy Destination:=Sheets("Sheet2").Range("C1")

.Range(Cells(1, 1), Cells(LastRow, 4)) _
.AutoFilter

.Application.CutCopyMode = False

.Columns("B:C").EntireColumn.Hidden = False
End With

Application.ScreenUpdating = True

End Sub


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk