I have a input selection, when i keyin XYZ, the data under column XYZ should be ranked in ascending order. If i select ABC then it should rank ABC data in ascending order.
XYZ
ABC
Input--->XYZ
XYZABC
15
38
26
42
Rank the column by selectionXYZABC
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies View RelatedI need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.
It is probably easier to look at the attached sheet and the previous discussion to see what I mean.
Previous discussion:
http://www.excelforum.com/excel-work...-of-cells.html
I'm trying to let the code described down below to select 10 of the highest values and give them a ranking in the next column using the letters A to J.
My query;
The code gives all 10 of the highest values the ranking letter J instead of A to J, so what's going wrong?
Also, is there a way to simplify this code so it will be easier to maintain and run quicker?
I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-
If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.
I have a customer spread sheet that uses a “Ranking” to establish the next “Contact Date” based on the “Ranking” and the “Last Contact Date”.
An example is – a “Customer” with an A “Ranking” would be contacted 21 days after the “Last Contact Date”, with a B “Ranking”, 28 days after the “Last Contact Date” etc.
Is there any way that the “Next Contact Date” can be automatically up dated when the “Ranking” is changed and there are multiple rankings (A, B,C, D etc)
I have a table that has multiple products and price points in it and I would like to add a field that ranks the products by price but for only against that product.
Product Price
Result needed is the Rank only by product based on Price
A
150
2
A
155
3
B
130
1
[Code] ........
i would like to rank one table based on two columns, for example world cup group table to be ranked based on points but if two teams have the same no of points, then use goals scored column to break tie between them.
View 9 Replies View RelatedI have a list of electrical item brands in column B and a list of item types in column C what i need to happen is when i select a certain brand with a certain type i would like the cost of the item to automatically enter in to column D!
Because of my rubbish description i have attached an example
Excel 2010. I have a macro, stored in a workbook.
The macro is: collecting data from another opened workbook,processing the data,saving the processed data in yet another workbook.
The workbooks' names it is operating on are hardcoded in the macro. I'm trying to make it more flexible, that is to allow user to specify both source and target workbooks along with the data columns.
The workflow follows:
User opens both source and target workbooks,User opens the workbook with the macro,User runs the macro (with a button for example),Macro prompts the user to select range in the source workbook,Macro "memorizes" the range and the source workbook's name from user's selection,Macro prompts the user to select range in the target workbook,Macro "memorizes" the range and the target workbook's name from user's selection,Macro processes the data.
I found out that this can be achieved with Application.InputBox("Select cell(s)", Type:=8) method. However there are some issues that I was not able to solve playing with the returned value's methods:
The .InputBox defaults to active workbook. I was able to select another opened workbook only with Ctrl+Tab combination, but it will not work with the end users - they are too poor with these tricks. Is there a better, more intuitive way to allow user selecting a workbook?I was not able to get the workbook's name from the .InputBox returned value property. I was trying with .application.caption and .application.activeworkbook.name - none of these worked. They returned name of the workbook with macro, not the one with selected range. How to get the workbook's name from given cells range?
Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.
Example:
20
18
18
15
12
11
11
9
The ranking I am looking for is:
1
2
2
3
4
5
5
6
I have data in a number of columns in Excel which I want to sort on column B, then C, (Then D) however the formula I have entered is sorting based on column C below in ascending rather than descending order, how I can change this
=RANK(C2,$C$2:$C$4)+SUMPRODUCT(--($E$2:$E$4
I've got 3 columns of info.. Name, Org, #Sales, $$..
I need to rank the list by:
Org, $$, #of Sales (as the tie breaker)
So, I need it to look for a specific Org Dept in Column B, rank the Dollar Amounts in column C, and if there are any ties, go to #Sales in column D and use the biggest number there to break the tie.
I've found 2 formulas..
This one was awesome for looking for the specifc Org Dept and only ranking those numbers that matched the designated Org Dept. (but this can't break any ties)..
IF(ISERROR(MATCH($O3,LARGE(IF($P$3:$P$53=R$2,$O$3:$O$53),ROW(INDIRECT("1:"&COUNTIF($P$3:$P$53,R$2)))),0)),"",MATCH($O3,LARGE(IF($P$3:$P$53=R$2,$O$3:$O$53),ROW(INDIRECT("1:"&COUNTIF($P$3:$P$53,R$2)))),0))
Then there's this one that broke ties, but can't recognize a specific Org Dept that I need..
=RANK($O3,$O$3:$O$53)+SUMPRODUCT(($O$3:$O$53=$O3)*($F$3:$F$53>$F3))
I need a formula that looks at the percentage values of 3 cells and the in the next row ranks them 1st,2nd or 3rd. So for example cells A1, A2 and A3 have 30%, 20% and 10% respectively, so in cells B1, B2, and B3 I need to return 1st, 2nd, 3rd. But next week it could be that A1, A2, and A3's values are 20%, 30% and 10% respectively, and so I'd need cells B1, B2 and B3 to return 2, 1, 3, and so on. Be cool if the rows could automatically re-arrange so that whatever is ranked 1 is always on top, 2 in the middle and 3 at the bottom, but not that important. Be also cool if the 1st ranked row, chnaged colour or flashed or something, again not important though.
View 4 Replies View RelatedI want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.
e.g. In cell C115 I want to have the value BDS05.
I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.
I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).
I am new in Excel VBA and working on developing a form for Quarterly Reporting. My userform (Quarterly Report) contains controls such as combobox (one of them). I am trying to have another Userform (Risk and Mitigation) to pop-up when a specific item is selected.
Example:
With cboRiskScale
.AddItem "High"
.AddItem "Moderate"
.AddItem "Low"
End with
cboRiskScale.Value = ""
I am looking for the code where I will be able to have the "Risk and Mitigation" userform pop-up for user input when the "High" is selected.
How to do a ranking for productivity based on goals in six categories for my employees. I want to be able to rank them on these key categories and rank them based on the best average. For instance:
Employee #001 Ranked 1st in Sales per Hour, 3rd in Avg Transaction Amount, 2nd in Multiple Sales, 5th in Accessory Percentage
Employee #002 Ranked 2nd in Sales per Hour, 1st in Avg Transaction Amount, 1st in Multiple Sales, 4th in Accessory Percentage
I want to factor against the goals to determine each individual rank for each category and then rank based on the combination of them all.
I haven't gotten to all the additional conditional formatting but recall how to do this with ease, but the sheet in its basic format is present in the attachment below.
Employee Productivity.xls‎
How to return ranking based on students' score?
STUDENT SCORE Rank MAY 11255 4 JACK 5262 5 TIM 432525 1 STEVEN 352332 2 HUGO 32232 3 FERNANDO 5153 6 SANDY 4556 7 SARAH 2265 8
I would like to change the ranking between two rows based on some criteria with macro.
View 6 Replies View RelatedI have a list of research material based off postcodes, travel time in those areas and what I want to do is rank the the travel times in those ares within a third column.
What I have so far in design terms is this:
Origin Base
Time
Time Ranking
AB10
00:10:00
AB10
00:12:00
[Code]...
So basically I'm trying to have column c look at a and check for the lowest value in b against all other rows that have the same constant value e.g. AB10 in column a. Then to distinguish them somehow for example marking the first '1'. Also then to be able to do it for second and thrid ranking value.
What do people think? Is this doable? I think maybe a min if array but keep tripping on the fact that the ranges are all locked in a single column and inconstant sizes.
The data comes in this format and is quite large so manual editing would be a last resort.
I have a speadsheet that has a table containing scores of audits, in the last 3 columns of the tabe I have the two totals (columns AC and AD)
In Column "AF" I wish to show where they will rank based on the results, first I need to rank via the result of column AD and if that is the same as another result use column AC to determine where they rank.
I have a scheduling tool that I need to do the following with -
Allow user to select a range of cellsUser types a value (non formulaic just plain text)Value gets populated to all selected cells
The reason I'm not just using fill handles is because of the amount of formatting in the cells.
I'm looking to populate tables for specific tasks that my site performs and compare their performance against the other top sites in the company. I need to pull the site # and their performance based on the task, ranking them from first to last.
View 5 Replies View RelatedI am facing some problem with ranking formulae. Attached is a file illustrating them.
Problem 1: Ranking without zero I have a set of data for products. They have a number next to it. Some of the products have a ZERO value next to them. I want to rank the data in an ascending order while ignoring the products with ZERO value next to them.
Problem 2: Ranking on multiple factors I have a set of data for 50 products. There are 5 factors based on which I want to pick the best and the worst. Each factor has a value which can be a negative number, zero or positive number.
This is what I am doing presently: Ranking the products on each factor in a separate column. Points are assigned based on the ranking, i.e. if the ranking of "product A" on Factor 1 is 32, then it gets 32 points It is assumed that equal importance [weight] is given to each factor, thus average points is calculated based on ranking of each product on each factor. Average points are again ranked in descending order The top & bottom 10 are picked from this ranking.
Number of products and number of factors can be different for each analysis. Presently I am giving equal importance to the rankings for each of the factors, what if I have to give varied importance [weight] to the factors.
I am trying to calculate players ranking based on their weight categories and attempts total, e.g. Category (50kg, 69kg etc..) Rank should be calculated automatically the highest total value of cat. 50kg is 1st second highest is 2nd etc. However, if tow players or more within the same Category have scored same point ranking will be calculated based on Body Weight less comes first and if both have similar weight calculation will be based on Start # first player comes first etc..
Table:
Start #
Name
Body Weight
Team
Category
Total Point
Rank
1
Player #1
49.2
Team #1
50kg
75
What I am trying to accomplish....
If the input changes in D5, K11 will reset to $0.00..
Example. If cell K11 is set to $300.00, and the corresponding input in D5 is '123', when I change the contents of D5 it sets K11 back to '0.00'.
i m creating a formula that will automatically place players in the correct position based on the outcome and there rank.
I have created an example sheet and the results should be in cells E3:E15
In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.
View 6 Replies View RelatedItem Quantity
A 2
S 7
D 3
F 6
I am looking for a ranking formula that will pull the data from the
first column based on the ranking of the second column
so that the end result will look like this
1 S
2 F
3 D
As S has the largest quantity, F 2nd largest etc
The list I will pull this from is variable in length but in the
hundreds.
let say i have in Sheet1 colomn A a list of names, i want to select the names and copy them to a second sheet (Sheet2) base on the below criteria
in Sheet2 the first name should be in B3 and second name in E3 the third name in B23 the fourth name in E23 the fifth name in B43 and so on .