i have an idea that i will be able to select a team in sheet1 cell a1 via a dropdownlist and also c1 giving a home and away team
all teams have there own unique number which i would be able to list them all
it would then look at c1 compare it against the list and use that number
then on sheet two
it would return the results
web address is [url]
arsenals number is 142
aston villa are 154
I'm writing a couple of macros which require selection of a range. The user could either select the range before executing the macro or if range has not been pre-selected, an inputbox should pop-up through which the user can select required range.
View 9 Replies View RelatedUsing MS Query in Excel, I've created a simple query that pulls its records from an SQL dbase. Here's the statement:
SELECT uvVisit.FacilityListName, uvVisit.DoctorListName, uvVisit.Date, uvVisit.PatientVisitId, uvVisit.PatientLast, uvVisit.PatientFirst
FROM CPS.dbo.uvVisit uvVisit
WHERE (uvVisit.Date Between ? And ?)
ORDER BY uvVisit.FacilityListName
The query runs fine and prompts the user to enter beginning & ending date ranges for the visit date when executed. So far...so good...but, this requires me to manually insert a line in Excel above the 1st record and type in something like: "For Date Range: MM/DD/YYY - MM/DD/YY" to denote the date range that the qualified records fall into (something the user wants to see).
However, I'd like to find a way automatically preface and display in the report's output (perhaps as the 1st line of the report in Excel??) something similar to what I'm already typing, and have it pull the beginning and ending MM/DD/YY values from those supplied by the user in the parameter.
I am organizing a sports tournament and would like excel to randomly generate the teams for it.
We have 20 people who will be organized into 10 teams of two. how to setup the formula to have two people randomly paired together without duplication. The names will be further used in other formulas for points calculations.
So, I have the names as A1:A20 and then would like the teams generated from B1:B10 and C1:C10 with the team being the row and the team members in the B and C columns.
i want columns T,U,V,W to continuously count wins and lost for the teams..if i
change the teams names i want T,U,V,W to read "0".
Any team can play any one.
also this is kinda a part 2 (really small part)
I'm trying to use Today function but cant get it to count every other day..
so for example tomorrow will also read "January 7th 2010"
Sheet14
MNOPQRSTUVWXY2
3
4
Team 1VSTeam 2W/L Count
5
LostWinTeam 1Team 2
6
WinLostWinLostWinLost
7 .......................
I need to create a sheet probably involving macros where I enter a numerical value into a cell (say B5) I want the sheet to assign a score to the value I enter and then add the score it generates to a table of values. Once ths is done I need it to automatically clear the original value I entered ready for me to enter a new value - rinse repeat..
e.g.
into B5 I enter 15 - on the right in the results table there are headings v w x y and z
say 15 scores one point for w and one point for z I want the sheet to add 0 to the totals under v,x & y and add 1 to the totals under w and z.
Then I want the sheet to clear cell B5 so I can enter a new value without manually deleting the previous entry.
If I can also copy the value I enter into a "history" which simply lists all the values I enter as I go along so much thebetter.
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
How i can take just team names in A column as home and away to B-C columns ?
View 4 Replies View RelatedI’m trying to do now is I have a drop down menu that allows me to pick any team that we can face in the season. Once that is chosen what I want to show is the individual stats against that team. Now right now I’m just trying to work out the formula to see if it works.
The formula itself is half working. For some reason some of the selections aren’t showing up like there suppose to do.
I have 12 teams, 4 divisions of 3... they play each team in there division twice, and every other team once... What's the easiest way to make something to randomize this automatically? This is for a contest at work that starts over every 13 weeks.
Obviously there can't be any duplicates week to week.
WeekABCDEFGHIJKL
1BAAEDDHGGKJJ
2BAAEDDHGGKJJ
3CCBFFEIIHLLK
4CCBFFEIIHLLK
5DDDAAAAAAAAA
6EEEBBBBBBBBB
[code]....
I need to create a full list of fixtures for a football (Soccer) league i am involved in.
i believe this is a multi stage project that i need to tackle in bite size chunks.
I have a list of teams, division that they play in and there home stadium name.
my spreadsheet has 2 sheets visible at the mo...
Rules:
I need to create a list of all games that require to be played.
All teams in the same division must play each other twice, once at the home stadium of each club.
Only division 1 clubs play against other division1 clubs
Only division 2 clubs play against other division2 clubs
I need a full list of matches to be generated in my worksheet called "required Matches"
I have a spreadsheet with 14, or maybe 16, or maybe 40 names in column B.
A1 = 1 B1 = JOE CITIZEN
A2 = 2 B2 = JANE PERSON
...
A17 = 3 B17 = JACK DOE
and so on...
I want to randomly assign these names to pairs using a macro button, and basing it on the number of players (C1)
So if C1 is 12, I'd like the function behind the macro button to come up with 6 * 2 numbers (2 and 11, or 4 and 7,
all completely random, but within the 12 specified in C1) and write these random values in Range D1:E6
(if C1 is 18, then the range to write in would expand to D1:E9).
I don't know enough about writing code to be able to pull this off .
I have a code that will create separate sheets from “SDL_Calendar” sheet for each team and its working fine. Modify the code to create sheets for each Team BY YEAR based on user selection in Cells “H6”, “H7” and “H8” in “P6_Report” sheet. For filtering BY YEAR Column "D" Can be Used in “SDL_Calendar” sheet.
I have attached the work book of what I am trying to accomplish :
[Code] ......
CreateSheetsByYear_new.xlsm‎
I am using Excel 2002 in Xp and also want to use the spreadsheet on my Pocket PC using Pocket Excel.
I have a column of 10 numbers with rows 1,3,5,7 & 9 representing the individual gross scores of the 5 team members. Rows 2,4,6,8 & 10 have either a 0 or -1 (calculated) in them representing the strokes (or discount) the player receives for that hole. So the net scores for each player will be row 1 + row 2 and so on.
I need a formula that will calculate the total of the 2 lowest net scores and 1 low gross score with the score for each player only being used once which will then represent the team score for that hole.
A sample column would have 4,0,3,0,4,0,3,0,5,-1 in it.
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
My friend and I have created a Wallyball league (volleyball played on a squash court). Every week we have more and more players and making the teams is more and more complicated and time consuming. I created this Excel file to try and generate random teams faster.
team selection.xlsx
The file consist of :Jersey no. and player (A and B)rating if playing (C)rating (D)playing on a given day 0 or 1 (E)wallyball courts to generate random teams with a team rating and differential rating.
The only thing it needs is a non-repeating function (as of now, one player can be selected many times)
Also, the players should not be selected if they are not playing on a given day (column E = 0)
I have to do this WITHOUT using macro (1. I'm not familiar with macros ; 2. I have a free starter version of Excel, I don't think macros are enabled).
Problem: Have a squad of 8 players in my darts side. We have 12 games in our season and each game requires 4 players so each player will play 6 games. 6 of these games are at home and 6 are away.
I want to create an excel sheet whereby a team will be picked for each match. Each player will play at Home and Away 3 times each. And the same group of players will not play with the other players each time.
eg. Week 1- Team Players, 1, 2, 3, 4
Week 2 - Team Players 5,6,7,8
Week 3 - 1,3,5,7
Week 4 - 2, 4, 6, 8 etc.
Where to start. There are multiple variables.
I'm working on a simple spreadsheet with individuals divided among teams. Each team is separated by a page break, so the column displaying Team Name repeats the same team name all down a page, and then another team name all down the next page, and so on. I've been searching for a way to display one instance of each Team Name as the Page Header (or just at the top of each page) upon printing. I've included an attachment for reference.
View 3 Replies View RelatedIs it possible to calculate the current optimal fantasy football team from the attached data and constraints listed on the 'solver' tab?
View 2 Replies View RelatedI am trying to build an excel spreadsheet for a hockey program I'm coaching that will do the following:
Create 4 teamsThere will be a Team 1 White/Dark, and Team 2 White/DarkUse 2 lists to distribute players evenlyThere are a disproportionate amount of players on each list. There are 8 Majors, 12 Minors. 2 Majors maximum need to be on every team.Every week, the roster will be different (some don't show). I need to be able to hit 'randomize' and exclude certain people, and still have the lines 'relatively' similar This is an essence two 3 vs 3 games, with a white vs. dark on each side of the ice (2 half-ice games). If there are 3 majors on each side (6 in attendance), one side will have a white with 2 majors, the other side will have a dark with 2 majors.
I'd also like a way to keep track of who's played with/against who, so if I can export that data set to ensure I don't get a duplicate team...or a primarily duplicate team.
I am trying to collect data from a darts team and these values reflect
the number of points won in a team game (ranging from 0 - 3). This will
probably be simple for the experts here, so I'll let fly:
I have a table with six names across the top. In the rows below there
will always be two cells that have number values ranging from 0 - 3
while the rest will be blank. Those two numbers however, will always be
the same. I am trying to write a formula that will determine which
columns have values and return a single letter value (first initial)
for each name. Since all the names are different that is not an issue...
I've been working on a userform that I've been adapting from a workbook I had set up to record real time stats. Its pretty much where i want it to be now and I am trying to focus on using the stats created by the individual users to populate team stats. There will be many users using this and many different teams. so i imagine, it wouldn't take too long for just one workbook to fill up and cause problems. Would i be correct in thinking that an IF statement in my current code (that's set up to send to one master sheet at the moment) that will pick out the manager/users name from the userform and send the data to the respective teams workbook, would solve this problem. Or am i over thinking this and is there a more simple option.
Code:
Private Sub UpDate_Raw_Click()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
[Code] .......
I have the following data in a number of cells and I would like to perform a count on match if a team has won, drawn or lost
Nottinghamshire beat Derbyshire by by 53 runs
Lancashire beat Yorkshire by 30 runs
Durham beat Leicestershire by 8 wickets
Midlands/West/Wales
Worcestershire v Warwickshire - No result (Match abandoned)
Gloucestershire beat Somerset by 8 wickets
I am using an excel where I want to send mail to my team members for their trainings pendings,
Few trainings are once in a year and few traings are monthly.
I have a list of nine teams from B13:B21 with Wins in column C, Losses in Column D, Ties in Column E. Pts are in Column F (all of these run row 13 down to 21). The team listed in B13 has a cell name of One in the Name Box, team in B14 has a cell name of Two, etc up to Nine.
The pts total for each team is also named, so F13 is called ptsone and has a current value of 8. F14 is called ptstwo and has a value of 10, etc.
I'm trying to do is in a different area of the spreadsheet put a formula that will compare the pts values and then list the team standings based on those points.
I am planning to calculate the individual contribution on customer satisfaction % for the entire team.
Currently the layout looks roughly like this:
C2 - contains overall team Csat %
C4 - contains total number of surveys received
C9 -> C15 contains operator names
D9 -> D15 contains operator individual csat %
E9 -> E15 contains operatore number of csats received
Using the data above I thought I should be able to generate a number or % that would indicate the individual's contribution to the overall score.
I am looking for a formula or VBA code that will allow me to Select a Team leaders name and all the agents reporting to that team leaders would populate. I have the first part of that problem solved but its getting the agents names to generate uniquely that I find problematic.
Below is a step by step example of how i intend it to work
Step 1
I select the Teamleaders name
Shelaine
Once that has populated the Team Names automatically appear
Shelaine
Score
Andrew
20%
John
30%
Mark
40%
Sarah
50%
June
60%
The scores will populate on its own as I will include a Vlookup formula to generate the scores, Its just the Agents names. I am using Excel 2007
I have a set of data like below, I am trying to get a formula to look at a range i.e. May column and then select the highest number in that range but rather than returning the number returning the team name, is that possible? Not sure where to start...
Jan
Feb
Mar
Apr
May
Jun
Team 1
25087
23029
32991
21319
25271
25136.8
[Code] .........
I have roster that has 12 teams on it, each team has their own column with the supervisors name in the first row. I want to look up the supervisor and count the number of team members listed under that supervisor.
View 4 Replies View Related