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

Finding Auto-Sort Macro From MIN Calculation

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

Sort Data Into Ranges?

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

Sort Data With Dynamic Ranges

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

Sort Ranges Across Multiple Sheets

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

Custom Sort Non-Contiguous Ranges

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

Adapt A Macro To Sort Multiple Ranges?

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

Sort: Multiple Varying Size Ranges

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

Sort Data Ranges Across Multiple Worksheets

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

Non Consecutive Rows Sort Data With Multiple Ranges

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

Lookup Formula: Commission Calculation To Be Done Automatically Once Data Is Inputted In Cell

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

Macro To Automatically Name Ranges?

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

Fill/Populate ListBox From 2 Ranges, Sort & Remove Blanks

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

Sort The Table Automatically?

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

How To Sort Data Automatically

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

Macro To Sort Automatically

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

Automatically Sort By Date

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

Sort A Column Automatically

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

Getting A List To Automatically Sort

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

Automatically Sort Table

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

Automatically Sort New Data

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

Automatically Expanding Cell Ranges?

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

Automatically Copy Ranges To Another Worksheet

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

Control Ranges Automatically Calculated

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

Automatically Add To Validation List And Sort?

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

Every Column Of Workbook Sort Automatically?

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

Automatically Sort Information Into Different Sheets

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

Sort List Closest To Value Automatically?

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

Automatically Sort Due Dates As They Change?

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

Sort Data. And Automatically Fills

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







Copyrights 2005-15 www.BigResource.com, All rights reserved