Automatically Sort Ranges On Calculation
Feb 5, 2008
I've created this fantasy football programe to calculate all the scores from all matches in this years European Championships.
Basically everybody ranks each team from 1 to 16, giving 16 points to the country you think will win the european championship, 15 points to the runner up, and so on down to the country, you think is the weakest on just 1 point.
Everytime I try and change one of the values in the 'Results' tab I get an "Compile Error/Syntax Error".
What this macro should do is sort all the data in the "League Table" tab into a highscore table list.
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("b1"). CurrentRegion.Sort _
Key1:=Range("h2"), Order1:=xldescending,
Key2:=Range("c2"), Order2:=xldescending,
key3:=Range("e2"), order3:=x1descending,
Header:=xlYes
Application.EnableEvents = True
End Sub
View 3 Replies
ADVERTISEMENT
Apr 21, 2014
What I want to do is place a lap time into 2 columns (representing each lap), then excel chooses the lowest number in the two columns on the same row, paste it into another column, and then automatically sort, while keeping the first column static for 1, 2, 3, 4, 5 etc
So what I want is
Column A | Column B | Column C | Column D | Column E
1 | Driver Name | 14.004 | 14.102 | 14.004
I want Column A to remain in tact, never changing, 1-50. I want Column E to hold the fastest lap time calculated from C and D. Then I want excel to automatically sort Column E from lowest to highest, while keeping Column B with a specific driver name in line with their respective lap times. Column A is not a 100% solid requirement, but auto sorting Column E after it's calculated the minimum value of C and D is.
View 11 Replies
View Related
Mar 12, 2013
sort data into ranges like in the example below:
1,2,3,4,5,6,10,11,12,15,19,21,27,28,29,30 [Each value is in a cell vertically]
into
1 to 6
10 to 12
15
19
21
27
28 to 30
provide the vba code for this ?
View 1 Replies
View Related
Jun 8, 2014
I currently have two tables in one worksheet showing the sales of different region.
The problem is, when I sort the data in the table (I can't used the named ranges as it should exclude the first row which is the header), is there a way I can make it dynamic too??
**Attached, please find the example spreadsheet, I have only written the code for the first table.
View 3 Replies
View Related
Jan 24, 2010
Sub DynaSort()
Dim wsSheet As Worksheet
iRow = ActiveSheet.Columns("A").End(xlDown).Row
For Each wsSheet In Worksheets
Select Case wsSheet.CodeName
Case "Sheet2", "Sheet3", "Sheet4"
wsSheet.sort.SortFields.Clear
Range("A3:I" & iRow).Select
wsSheet.sort.SortFields.Add Key:=Range("F2:F" & iRow) _
, SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
wsSheet.sort.SortFields.Add Key:=Range _
("H2:H" & iRow), SortOn:=xlSortOnValues, order:=xlDescending, DataOption:= _ ...................
The problem that I has is that I cannot put focus on a cell after the sort. Xl keeps the columns selected and then when I'm trying to put in the next data Excel selects all the rows in Sheet1 also. I know how to get rid of it and continue, the users on the other hand are not that experienced with excel. fun thing, even thou the, Range.value is inside the IF it putt "pucko" in sheet1. I have a code that copies the data and then put some several functions in each sheet, after that I call the sort routine.
View 8 Replies
View Related
May 21, 2008
can anyone modify below macro to sort this data:
before sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-6-5-99-2-1-99-4-3
3-99-7-8-99-5-6-99-8-9
4-99-8-7-99-6-5-99-9-8
etc
after sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-5-6-99-1-2-99-3-4
3-99-7-8-99-5-6-99-8-9
4-99-7-8-99-5-6-99-8-9
etc
Sub SortNoncontiguousRanges()
Dim rRange As Range
Dim lArea As Long
'10 rows in columns B and C
'10 rows in columns E and F
'10 rows in columns H and I
Set rRange = Range("B1:C10,E1:F10,H1:I10")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
.Sort Key1:=.Cells(1, 1), _
Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
End With
Next lArea
End With
End Sub
View 9 Replies
View Related
Jun 18, 2013
I'm trying to adapt this macro (without success) to sort a number of ranges rather than just one:
Dim rng As Range: Set rng = Range("B11:F45")
With rng
.Sort Key1:=Range("C11"), Order1:=xlAscending, Header:=xlGuess
End With
Set rng = Nothing
End Sub
I want to ADD some further parameters
to set Rng H11:L45 and Sort Column I11:I45
to set Rng N11:R45 and Sort Column O11:045
There's many more but I've tried to add in ranges but it won't work.
View 1 Replies
View Related
Dec 4, 2006
name data1 data2 total
chris a b 100
a a 100
a a 100
steve b b 90
b a 90
b a 91
jane b a 89
a a 90
a a 92
its hard to line things up in here..... anyway... each name has two more lines of data associated with it. i want to take the 4th column last line for each and sort from greatest to smallest and rearrange all the data but keeping all three lines associated with each person together. one added complication. the number of ppl in this will vary although i can do a count of every three lines and tell exactly how many there will be. so for this purpose we can just keep it at three.
in this case, all three lines associated with jane should be moved up above the three lines of steve but stay below the three lines of chris. and the three lines for each person stay in the same order they are now
View 9 Replies
View Related
May 2, 2008
I have been asked to create an attendance worksheet where employee names and data are entered on a "main" worksheet and hours are entered on monthly worksheets. The names on the monthly worksheets are referenced from the main worksheet. Therefore, if I add a name and do a sort, the names on all pages will move, but the data will not. I imagine I will need an ID column to help sort. How do I make a macro to do the sort?
View 2 Replies
View Related
Feb 23, 2010
Once again, I'm seeking some further assistance to an issue that I have yet to resolve.
I have several rows of data which I'd like to be able to sort by the info in column "A".
The data rows are not all consecutive. The range for which the data resides are:
Range("A25:A73,A85:A105,A145:A193"), When I sort I want my data to stay within these limits/ranges.
View 8 Replies
View Related
Mar 25, 2009
I am trying to come up with a formula that will allow the commission calculation to be done automatically once data is inputted in cell A2 and E2. I have tried IF statements, but can not figure out how to make it work. I am not able to figure out how to get cells F9 and F19 to work with the proper formula.
View 4 Replies
View Related
Jun 29, 2012
I have a sheet containing employee data - 13,000 rows - one for each employee.
Column A contains their store number (there are about 1300 stores in total) and column H contains their name.
Is it possible to create a macro that, for each change in store number, will create a named range for the employees' names in that store automatically?
View 9 Replies
View Related
May 18, 2008
I need a listbox or combobox in an excel form whos list is created from 2 ranges. for example first name is range a2:a500 second name is rangne b2:b500. the list box should list both coloums in a single list and return the row number. better still if it does not list blank ranges. i have never used a list or combo box before so please explain in terms a simplton can undrstand
View 7 Replies
View Related
Apr 24, 2014
How To Make the Table Sort Automatically Without using Sort Button? For Example, I Changed one of the Cell's Value. Then The Table Sort itself.
View 2 Replies
View Related
Aug 11, 2013
i have a load of data that i dump onto a sheet and i would like to sort automatically with out the need to do it via data and sort
I have attached an example
Can it be do within a lookup?
View 3 Replies
View Related
Nov 8, 2008
I am now trying to write a macro which is a little over my head and was hoping some one may have some idea how to do it or have some code that is similiar and I can try to customise it myself. So here goes.
Basically all that happens is I paste data into a work book that always has two columns. The first column is a list of names and the second column is a list of numbers. I need to sort that information automaitcally every time it is pasted into the work sheet as follows.
1) Once any information is pasted to sheet 1 run macro automatically with no user interaction
2) All numbers must be rounded up or down first before sorting. If it is say 2.4 then it would become 2 and it it was 2.6 it would become 3.
3) Then move the numbers only from a specified column from sheet1 to sheet 2 and split them up into seperate columns based on the following results.
a) 0 - 3 gets moved to column B
b) 4 -7 get moved to column C
c) 8 - 10 gets moved to column d
4) Delete both columns from sheet1 after this is run so the user can re paste new information
5) If a user does this again then do the same but move into a new set of columns e f g and so on until the work book ends.
View 13 Replies
View Related
Jan 14, 2010
Here is what I want to accomplish:
I have a spreadsheet with data in cells A36 to G52. In The D column (D36 to D52) I have a date in there. I would like to have this column sort in ascending order automatically, but also have it sort the other data within the same row with it.
I have tried a couple of VBScripts that I have found in other posts that are similar, but I just get errors when trying it so I assume that I am not putting something in correctly.
Can anyone help me write this functionality? I have other data in a similar setup on the page that I also want to sort automatically but the script for this first part should be just a copy and paste, and change a couple of values....at least I am assuming so.
View 14 Replies
View Related
Apr 29, 2006
Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered.
View 11 Replies
View Related
Oct 17, 2013
I am trying to get a list to sort automatically. I am running a competition over an 11 week period and entering weekly data that is totalled on a separate "Totals" sheet.
This "Totals" sheet, in turn, feeds a "Scores" sheet which I want to automatically sort in descending order of score in order to show the current positions in the competition.
The data in the list isn't entered directly but comes from formulas that reference cells in other workbooks.
I have found ways of sorting a list when you enter data into the list itself but what I need is a macro that will update the sort order automatically whenever a new value is entered in one of the other workbooks.
View 1 Replies
View Related
Nov 21, 2006
I am currently in the process of creating a league table using Excel, and was wondering how I could get the teams to automatically arrange themselves in order of total points?, without using macros, is any formula capable of this?
View 9 Replies
View Related
Jan 6, 2007
I have (what seems to me) a somewhat complicated request. I’m trying to do a custom workbook without using VB (if possible). I’m trying to design a file list that can be updated easily by anyone. The new row of information needs to be entered (by a data form maybe?) and then automatically alphabetized by ‘ case name”. See below. I don’t know Excel formulas that well. This seems fairly easy, but I am at a loss how to do it.
Case NameCase #Open Date Closed DateRep
1Apple, Aaron2006-45 12/8/06 Tom
2Apple, David2004-02 01/14/04 Bill
3Bennett, Tim2006-0411/28/06 Bob
4Carter, Jimmy2005-23 04/22/05 Chris
5Carter, Tim2000-11 08/02/00 Jim
6Ford, Gerald 2007-206/06/05 Jill
7Gold, Bill 2006-7803/12/04 Pat
8Hill, Jim 2006-7703/18/04 Amanda
9Janes, William2005-6803/14/05 Dave
I would like to enter a new record (“case name -e.g. Jones, Smith, etc.) , with all the other corresponding info (Case #, Open Date, Rep #) and have excel automatically insert it ALPHABETICALLY by case name into the sheet and also, if possible, renumber. I know I can add the new record it to the last row and the Sort by case name, but I wanted to have others do it, and when they try to enter and sort the spreadsheet it always gets messed-up.
View 2 Replies
View Related
May 8, 2014
The table below has three rows and 7 columns. Row 3 shows cumulative sums of the values in Row 2. Sometimes, Row 2 may not have values (not even a 0). The formula for C3 is =SUM(B2:C2) and =SUM(B2:G2) for G3. The challenge is that I have a very long row and summing it individually would be inefficient.
Is there a way of automating the formula so that the cell ranges expand automatically which I could use to fill to the right?
A BCDEFG
1Row 1123456
2Row 21041503
3Row 3115202023
View 5 Replies
View Related
Aug 22, 2006
Ok What I want to do is copy the info in Cells A1:N55 into cells A63 onwards, now this I have completed so far with the following code, but what I also want to do is copy any changes that are made in cells A1:N55 from when this copy function is run into cells A63:N118.
Does anyone know of any way I could go about doing this?
Sub CopyJob()
Dim wSheet As Worksheet, strWS As String
For Each wSheet In ActiveWorkbook.Sheets
strWS = Left(wSheet.Name, 2)
Select Case strWS
Case Is = "AJ", "CJ", "PJ" .......................
View 9 Replies
View Related
Jul 15, 2007
When you have a formula that contains calculations using other cells, the formula will recalculate every time that referenced cell is changed.
In my attached spreadsheet in the June 2007 Worksheet, in Column C10+ I am calculating realtime pip values given a stream of data from a DDE server. The DDE server values are in the Data tab. For ease of use I have also added a manual setting as I am sure no one here has the DDE server in question.
My problem is everytime the DDE server (Or manually as it may be) changes the data field, the values in Column C and F on the July 2007 WorkSheet get changed.
I would like Column F to only calculate one time (At the time of entering the trade).
And I would like Column C to Stop updating and use the last updated value once the corosponding cell in Column G has a field data "Closed".
This is very important to me as if it keeps updating over time, then a couple weeks into trading, the pip values may have adjusted substancially giving incorrect $ values in other cells.
View 9 Replies
View Related
Sep 24, 2007
I have created a validation list which automatically adds any new values a user enters.
Is there any way of sorting the validation list every time a new value is added to the list?
View 9 Replies
View Related
Mar 6, 2014
This code sort data of every Column of workbook.
I want sort only range e.g (c10:c61) of sheet(10) correct this code :
[Code] .....
sample.xls
View 1 Replies
View Related
Nov 30, 2008
I have a sales sheet that records every sale as it comes in. I want it to copy the relevant information (only that sales reps sales) into each sales reps individual sheet automatically so that I can send each rep his own sheet. Can this be done? SEE ATTACHMENT
View 2 Replies
View Related
Mar 21, 2013
I have a list of Y-intercepts (Column B - highlighted in Yellow) and I'd like to sort it based on a certain value (in J4) and get the 10 closest numbers above that value and the 10 closest values below that value followed by the remaining numbers.
View 7 Replies
View Related
Aug 15, 2014
I'm attempting to get a column of due dates to update automatically every time a date changes. The due dates are in B3:B15. There is a header in B3. The day after the actual due date, it is automatically changed to the same day on the next month (showing the next due date). I need the spread sheet to automatically sort the due dates from the closest due at the top, to the furthest due date from today at the bottom. Running it manually, or sorting it manually every time won't work. This is for an elderly couple who are having problems keeping track of everything each month. I just need something that will show them the upcoming bills and how soon they are due when they open the workbook. Anything other than opening the workbook is going to be too complicated. I'm new to macros. I've tried to modify some I've found in other threads, but no luck (lots of error messages). I don't understand it enough yet to figure out what I need to change. I've attached a sample sheet that shows the part of the workbook I'm having trouble with.
View 4 Replies
View Related
Feb 2, 2010
Scenario: You have two columns in a spreadsheet. Column A is a text input and column B is a tracking number for each row. Using the ISTEXT statement, I had it configured so that when a cell in column B recognized a TRUE statement in an adjoining cell in column A it would then assign a number. B1 is assigned a number when A1 has text.
What I would like to happen is build the spreadsheet so that after the user enters text in column A, column B automatically fills in so that the user doesn't have to manually enter it. I've figured out how to do this if the data is never sorted, however, if you sort the data then the tracking numbers don't follow and are really a mess.
Is there a way to auto fill column B and at the same time, make it so that the tracking number that is originally assigned to the row follows with A when sorted?
I've tried using =MAX but run into circular reference issues.
View 2 Replies
View Related
