i am setting up registrations for workshops at a conference.
each room holds 75 people.
what i would like to do is put the total room capacity at the bottom of the column and then, each time i put a check mark in the column, have the column total descend to zero so i know when i have to stop accepting registrations for that particular workshop.
is there any formula that would allow me to do this?
Returning Descending Values
I currently use the following to find the max number is a column:
=MAX($P$4:$P$65536). If I wanted to find the second highest, then the third highest, etc.. how would this be written?
Is there a way to use the lookup function in reverse? The "lookup vector" on mine has to be descending, that is it it is a column going from high values at the top of the page to lower values at the bottom of the page. When using lookup function in this setup, excel gives me to wrong answer for some reason. If I make the lookup vector ascending, it works, but unfortunately the vector has to be descending in my worksheet.
View 2 Replies View RelatedTwo questions:
1) How do make values on x-axis as words? E.g. apples, oranges, etc.
2) Is it possible to have the numbers on x-axis in descending order? If I can figure out 1), I can do this as well, but it would be nice to know if it is possible to have the numbers on x-axis in descending order.
I have some sheets which I would like to loop through and use sort descending on column S, would somebody help me out with the code to do so? I actually have the looping code already, I just need the sort descending bit.
View 5 Replies View RelatedI'm running a workbook that has a list of all the runs for a day, and several daily driver sheets that automatically copy and order the drops using vlookup.
If you look at run 1 on the run listing you will see that holmfirth is the first drop and sandbach is the last drop.
This is also the case on page 1, as it should be.
However, on run 7 you will see that the first drop is Edinburgh and the last drop is Biggar.
The problem is that on the run listing form, we sort the run backwards so that it is loaded onto the trunk wagon backwards, then the load is tran-shipped (swapped) to the delivery wagon (dont ask) and the last drop on the trunk wagon (Biggar on the front end) becomes the first drop(on the back end) on the delivery wagon now in the correct delivery order. I need the daily sheet (page 7) to reflect the fact that Biggar is the first drop and Edinburgh is the last drop.
I have numerical headers in each column that I am trying to sort in descending order (Most to Least) . I have about 9500 columns to sort. i am just looking to move the columns in a sort order not actually sort the data in the columns. Here is a sample below.
From this:
HTMLSheet3 Â ABCDEFGHIJKLMN
113648576535355315071349485476152746320913334742372721211628213677181341133173311312012040
114169400000200021040544100412070185414260509119080360310070020100401200118103446282223023310120122413
to this:
HTMLSheet2 Â ABCDEFGHIJKLMN115271364134913338576535355315074854764744632092773724127212116281361318133401016733
113121209114420040000200010518401441004127025463600105091198000371200102010401008101100243446282223233132012
I've sorted it by column A. However, sometimes the date is the same and then it doesn't always go right... see f.ex. 17 dec, where the ship arrives in the harbour and then is at sea again and then departs Not exactly what happens. I had the same problem on 28 dec, but there it disappeared when I told Excel to sort secondly on column B.
However, this doesn't work for 17 dec.
Don't know if macro is better, but I would like to try it with function
I have 2 sheets.
Sheet 1
Column A: Name
Column B: Points
Exemple:
Name Points
Mary 40
Johnny 20
Erick 60
Ralph 30
I want Sheet 2 to get data from Sheet 1 and sort by descending order automatically.
Then it should be
Sheet 2
Name Points
Erick 60
Mary 40
Ralph 30
Johnny 20
I don't want to do it manually every time I change some value; I want it to do automatically; so if I change, for example, Erick's points from 60 to 10 in Sheet 1, Erick should appear on the last place in Sheet 2, with his new value (10)
How do i achieve the graph that is on the pdf with the data in the workbook i've attached. i cant work it out?
View 2 Replies View RelatedI am looking to create a seperate league table based on percentages in two tables in descending order.
What i want is the not ready times in one ranked table and the call logging in another along with the persons name and percentage.
Im stumped about what is the simplest way to do this.
I have a spreadsheet that I am using for budgeting and I want to be able to track my savings by logging data from one cell.
If cell A1 represented my savings I want that copied to B1 then the next day when A1 changes the new value would be copied to B2 and so on.
Any way that I have tried so far B1 always gets overwritten when the new value comes along.
I'm using a table with columns from A to F and an unlimited number of rows. Note that there is a title at the top of each column.
-I'd like my datas in column F to be automatically sorted in descending order from top to bottom when I hit the enter key or when the number is entered in last column (column F).
-the the entire row of data needs to be sorted according to the sorting criteria of column F ( datas entered need to stay on the same row)
-the macro should validate that the number value entered in column F is the same as the number value entered in column A. (to make sure that there was no typing mistake)
-Result in column D must be the difference between the corresponding value in column B and C (B-C=D)
*The next couple of constraints are not absolutely required, i'd like to have them only if it's possible to add constraints with filling color:
-Whenever there is no value entered in column C on a specific row I'd like the entire row to be colored in yellow (fill color)
-IF a number is entered in column C, and that the result of B-C in column D is not equal to zero, I'd like this specific row to be filled in Red.
-when all the values are entered in a row from column A to F and that the result of B minus C is equal to Zero in column D, i'd like this specific row to be filled in a green color.
***in other words, the datas in each rows from column A to F will be entered on 2 occasions; on the first time, only the datas in column A,B and F will be entered (so basically after entering this set of values the row should be filled in yellow. the second time, the remaining values will be added to the rows, so at this time A,B,C,D,E and F will all be entered. when this is done there will be two options
1-the value in column D is equal to Zero then the entire row should be filled in green or
2-the value in column D is'nt equal to zero, then this specific row should be filled in red.
Keep in mind that the datas of a row should be automatically sorted only when the value in column F is entered or when the enter key is hitted to add a new row.
Is it possible to allow Autofilter but disallow Sort Ascending/ Descending for an excel list in sheet? I have data set up as an Excel List (Excel 2003, with Autofilter turned on) so all formulas, formatting etc copy down when a new row of data is added, however if I just Protect worksheet and disallow Sorting, the List features don't work. I also tried putting the following in the Worksheet Activate event
ActiveSheet.Protect AllowSorting:=False
But this doesn't seem to disable the Sort option in the Autofilter, only the Data Sort function.
I'm being asked to report on some survey findings containing 60 questions for my company. I've rated all 11 departments from 1st to 11th place for each question.
I want to be able to display a graph showing which departments came first, second and third and also where my department came - in the example of the first question, 7th.
What I'd like is a bar graph (ideally) without any values on the Y axis, the department name along the X axis and text labels at the top of each bar showing the position (ie. 1st, 2nd, 3rd and 7th).
The problem is that if I produce a graph from the following table:
Dept 1 - 1
Dept 2 - 2
Dept 3 - 3
My dept - 7
..then it produces a bar graph with bars in the opposite direction; ie the best department having the smallest bar.
I could just reverse the figures with 11 being the best instead of 1, but then the text labels on top of each bar are wrong. I know I could just label the correct figures by hand with text boxes, but I have 60 of these things to produce!
The closest I came was using the reverse values and having a custom text format to replace the numbers with the opposite text values ie. 11="1st";10="2nd"; but I couldn't get that to work
Any ideas? ....
i have tried: =VLOOKUP(TODAY()-7,A2:M2000,3) to return data that corresponds to a date 1 week ago. Unfortunatley, it does not work because my dates in the array are in descending order. The order cannot be changed... is there another function I can use to return the value I am looking for?
View 4 Replies View RelatedBased on the table below, I need two tasks.
1. Sort out the data descending for dates
2. Have a way so that I can only retrieve the latest information.
Meaning if I ask for Pen, I should only get a value of X
Similarly for XYZ, I should only get a value of N
Date A B Types
Value 5 Oct Pen X 5 Oct Pencil Y 5 Oct Pencil H 4 Oct Glue
Q 1 Oct
Pen N 27 Sep
Glue T 2 Sep
XYZ N 2 Sep
XYZ X
I have a macro built that sorts my data by column in descending order depending on what column of information is selected from the drop down menu.
However I need the macro to sort column F in ascending order, and continue to sort the other colulmns in descending order when selected.
I can't get my macro to switch the sort from descending to ascending order for this one column. Looking to see if this is possible, and if so what macro formula I would use.
Here is what the macro i am currently using:
Sub ddSort_Click()
Dim iDdSortVal As Integer
Dim CurrSheet As String
Dim lFirstSectionRow As Long
Dim lLastSectionRow As Long
Dim strCol As String
Dim lRptHeaderRow As Long
Dim iRptMeasColumn As Integer
CurrSheet = ActiveSheet.Name
[Code] .......
I would like to call a macro which would sort rows 4-23 from the highest to the lowest, or if it is already highest to lowest, then sort from lowest to highest. This is how I am doing the current sort(High-Low):-
Rows("4:23").Select
Selection.Sort Key1:= Range("E4"), Order1:=Range("e1").Value, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("A1").Select
I have an Overview worksheet that is pulling info from all other sheets in the workbook.
What I want to do is order that info in ascending order depending on the worksheet name.
So I want all the worksheet names that start with 'AB' say sorted in ascending order and all worksheets that start with 'BC' in ascending order.
Now this I have at present working by a macro I created which once the info is loaded onto the sheet I just selected the info and ordered it in ascending order. The thing is though the next time I enter this Overview sheet I might have an extra worksheet that starts with 'AB' and this will not be included in the order, I will have to adjust it everytime so I need a more automatic ordering process.
I have a list of numbers in cells A1:A10, these numbers change frequently. I would like to set up a formula to calculate the 3 lowest values from those numbers and list the three lowest values in cells J1:J3, with lowest value on J1 and the highest of the 3 values in J3.
I know I can use the small/large functions for this, but im not sure how to work it out to use just the 3 lowest values.
how can I change a bar graph horizontal time line from ascending to descending?
View 1 Replies View RelatedFind the sorting method of dates in excel 2010. I have dates in one column and i want to sort it, but I am not able to sort in ascending or descending order.
View 1 Replies View RelatedI wanna sort these numbers in descending order and related to each others in excel 2010.
H
G
F
E
D
C
B
A
1383.99
1533.954
831.197
1533.954
1533.954
1383.99
700
1533.954
1
[Code] .........
About descending and related to each others, I mean for example : #700 in column A,B,C,D,E,F,G,H places in the same row and the same happen to #1533.954 and others. About the numbers that are The One ( like 549.894), I need to put them in its column and a new row with empty cells in its other columns. In the end i want a table like this:
H
G
F
E
D
C
B
A
1533.954
1533.954
1533.954
[Code] .........
How to rearrange slices of pie chart in descending order without sorting the data?
View 1 Replies View RelatedI'm fairly new to using excel and have been trying to create a simple spreadsheet that keeps track of scores and automatically displays the names of the players in descending order. Anyway I have gotten to the point of using hookup to match a value and return that players name. But when two players have the same score it will only return the first found value. I sort the number scores using a LARGE function and it works great. Then I use something like this =HLOOKUP(Y2,B27:K28,2,FALSE) to match that value with the players name. How to return the other players name when the scores are the same?
View 7 Replies View RelatedI have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).
=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)
Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.
Is it possible to make excel sort ascending or descending but from mid way through alphabet and then loop through the alphabet again. for example. If Cell A1 had "A" in, it would sort as normal.
Cell A1 = A
Cell A2 = B
Cell A3 = C
Cell A4 = D
Cell A5 = E
If Cell A1 had "C" in, it would sort from "C" through the alphabet and then loop to the start of the alphabet as shown below.
Cell A1 = C
Cell A2 = D
Cell A3 = E
Cell A4 = A
Cell A5 = B
there may even be an excel formula answer to it rather than a macro. Coloumn A has a series of numbers, I want column B to show those numbers in what would be there Descending Sequence.
Thus
Col A
100
97
14
87
32
108
21
So with this routine you would then see
COL ACOL B10029731478743251081216 The above I suspect is not too difficult, however if two (or more) numbers are the same I want the sequence to reflect that thus COL A COL B100297 314 797 332 5108 121 6 and no... the data - Col A - cannot be sorted, it must stay in that sequence.
If possible I would want this done automatically when any Col A value changes (any unused cells would be 0 by the way). Oh, there will in fact be 25 cells in column A for this.
i want to sort column D acsending, then column C descending. i want to do this using VBA because i cant just format the sheet because another vba macro paste the sheet thier which would rid the formatting. (i would do it myself everytime but it is for someone to make it easier for them and not make mistakes..i searched, just found a sum formula post)
View 3 Replies View Related