Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
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" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "Blissfully Ignorant" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
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:D")) Is Nothing Then Exit Sub Dim LastRow As Long Sheets("Sheet2").Columns("A:D").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 Replace @mailinator.com with @tiscali.co.uk "Blissfully Ignorant" wrote in message ... 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" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "Blissfully Ignorant" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|