I have been working on a spreadsheet to manage my players in a virtual sports game. I have worked out that the optimum skill scores for all the positions and want a way to compare a player to the optimum.
1st2nd3rd4th5th6th7th Position 147.1737.6742.0533.8313.6711.3317.33
These are the averages for Position 1
How do I find the player who closest matches this optimum. Players have scores in the same 7 skill areas.
I am attempting to upgrade a scoring system in excel 2003 which I use to rank wins in a sports results grid and i have to amend a formula based on a new version. The current formula in the attached part-completed worksheet Draw9 of 1 to 9, which I wish to amend, is in cells AM42:53 one of which is:
I have now added an extra column of data in cell AN44:53 under “LSD” and I would like to use this to improve the accuracy of the original ranking. In other words I wish to Rank teams using the “Wins” in column AL42:53 plus the “LSD” data in columns AN44:53 and this will ensure that where teams are tied on the same number of wins then the numbers in the “LSD” column will enable a clear ranking i.e. say 3 teams on 3 wins ranked 6th = will now be ranked 6th,7th & 8th.
I built what I can only describe as a fantastic spreadsheet. It wasn't until after that I realized it is 123 MB in size, but even for the complexities within it, it still feels like that is quite large.
I can't share the file due to proprietary reasons, but any general tips as to things I can do to make it smaller (and therefore faster to run).
I tried saving it as a binary spreadsheet and that dropped it to about 80 MB, but I don't really know what that does and if it will impact the sheet performance in any way.
I am trying to set up a spreadsheet which automatically calculates the start and end dates of project tasks, by looking at the order in which tasks need to be completed. I have attached a spreadsheet to show what I am trying to do.
I have created a spreadsheet containing about 30+ macros and then copied it with a new title eg, sheet2013.xlsm (original) and sheet2014.xlsm. My problem is that every time I open the 2014 sheet it re-connects to the macros in the 2013 sheet (if this makes any sense). Is there a way to lock macros to a spreadsheet, ie open both sheets and use the macros within those sheets?
I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.
So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.
My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.
I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.
Code: Sub MergeSelectedWorkbooks() Dim SummarySheet As Worksheet Dim FolderPath As String Dim SelectedFiles() As Variant Dim NRow As Long Dim FileName As String Dim NFile As Long Dim WorkBk As Workbook
The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.
Project Description ... 1111E.000000001
I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.
In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.
It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.
I am trying to get excel to open a MS Project file and then run a project macro on it.
What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.
For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder
EXCEL CODE Sub ImportMSProject() Dim FileToOpen Dim mpApp As MSProject.Application Dim prjmacro As Object 'Identify the File to Open - START FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp") If FileToOpen = False Then Exit Sub End If 'Identify the File to Open - END
I have a column labeled W-L, In each cell there is a W or an L .How can I total up all the W's and L's and have the total count show a 21-13 (21 wins 13 losses). Also the scores of the games are listed as 5-4, 3-7, for each game.how can I add up all the runs for and runs against to give a total xx-xx
i'm using a macro in excel 2007 that will open a Microsoft Project file, and go to the filtering menu. This is working fine however i have come to a deadend as i need the answer from a Data validated menu to be inserted into a box that project creates. Basically i need to copy the text from my excel (worksheet 2, cell c2), into my vba formula so that it automatically inserts into the MS project box. I hope this all makes sense and would be massively gratefull if you could point me in the right direction.
I have many spreadsheets with sports stats. I pulled the information from the web. So on one page I have the stats. The next page I want to be able to type the teams name and have excel pull out all the stats I want. Makes it easy to compare teams stats that are playing each other. Is this possible I tried to use most of the lookup functions but I just cant figure this out.
I have not found a complete excelsheet that works to my wishes... so I thought maybe you guys could help me.. This is quite a big request. What I want is to add an sheet with a automatic league table to my spredsheed. I already have all the fixtures and the reports will be filled in as the season goes on.
(so what i want is that when i fill in the results of the fixtures there will be an table that automaticlly updated. The +/- statistics isnt essential. I was regarding to +/- in total sets...)
I attached the spreadsheet.
The games will be played as a best of three stets so there will be no draws.
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
Workbook 1 has 2 spreadsheets. Spreadsheet 1 contains Item and Pass/Fail Columns. under the item column is the serial number of the item tested. the Pass/fail column has the serial number duplicated if it failed tested. what is the formula is to have spreadsheet 2 pick the items from the pass/fail column on spreadsheet 1?
A coworker is setting up an MS Project...project, and adding a custom field that would convert the Start date field into our Fiscal Quarter Fiscal Week schema, to display as FQFW, e.g. Q1W1. I've written the formula in excel, but I've never even touched Project until this week. Apparently it doesn't take formulas quite the same way as Excel does. I tried using the ''Switch" function in Project, and it worked, but it only accepts 14 arguments, and there are obviously 52 weeks we're dealing with. Would there be a way to do this using VBA? (I know next to nothing here as well.)
I have attached a document paralleling a document I am working on. The dollar amount in each spreadsheet represent sales. I have entered in values into the candy, soda, and chips spreadsheet. I have also linked values for candy into the total spreadsheet. My question is can I somehow type something or drag the formula down to populate the other cells in the total spreadsheet?
The idea I am thinking but which I don't know how to implement is to list all the items (as in column G) and list all of the relevant cells (e.g. B1 in the Candy spreadsheet) as in columns H and I (Note that all items will have the same cells but the cells will have different values...e.g. all three items have a cell B1 and B2 in their spreadsheet but these cells contain different values). I then try and fail to create a formula in cell B3 of the Total spreadsheet. I am trying to create a formula of the following nature:
='(Spreadsheet Name From Column G)'!(Cell Name From Columns H and I)
The Second half of the formula doesn't really concern me (i.e. the cell name from column H and I). However I am perplexed as to how to achieve the goal in the first parentheses above.
I'm currently using an HLookup formula to create an output tab for a huge set of input data, but whenever I add new rows to the input tab, I have to adjust the formulas for each individual output cell.
For example, my output formula is currently "=HLOOKUP(B$4,'Inputs'!$B$2:$BJ$384,116,FALSE)," but if I add a new row to the input data, I have to adjust the table array and the row number for the formula. So, if I added a row before the 116th row, but wanted the output to be the same for this cell, I would have to update the formula to "=HLOOKUP(B$4,'Inputs'!$B$2:$BJ$385,117,FALSE).
Is there any way to adjust this formula so that it will automatically update to produce the same output without manually updating the formula each time I edit the number of input rows? Would an index/match formula work, and if so, what would it look like?
I have a chart with blank values in some cases. I have set up the chart to show those values as gaps, which relies on the blank cell being empty. However, my blank cells have a formula along the lines of: if (condition,value,"")
So the chart does not consider them blank, presumably either because the formula is in there, or because "" is not the same as blank. How can I make this cell appear blank for the purposes of the chart?
I have a spreadsheet that I use to track my stocks. I have installed a third party add-in that goes out to the internet and grabs various stock information. But, when you are pulling info on about 30 stocks and getting info like name, price, dividend, div pay date and such, it slows the spread sheet down tremendously. Is there a way to keep the value in the cell and change it only during a certain time period such as 5 minutes before the top of the hour to five minutes after the top of the hour.
At the moment if a pupil has done foundation listening then I write their numerical result in, for example C3 or if they have done higher listening I write it in F3 (and it works out the UMS and then the grade), but I wondered if there would be any way to just put a dropdown menu in a column for each name, or change it so that I can type in H or F that will change which data is used? Because the UMS and grades are different depending on whether the pupil has done higher or foundation I am unsure how to format it
As I am a fresh user of excel 2007 I do not know how hard it is to solve the problem I am facing but hopefully there is a solution that the readers here are willing to share with me.
I have a list of **-s that I have recorded as a hobby with my friends (karaoke, free concerts...) and I have them coded in a specific way, so that I can find them on a shelve easily. The list is built up in a form that is seen on the example file that I added. So far I have used my own handwriting and a sticker to label my ** boxes. It´s not a very good looking combo to be honest. Since I already have all the data in my excel tabel it would be good to use excel to take all the things from there and just print it out. How to do that? The front cover is easy, I used VLOOKUP function. All I have to do is enter a number into the column C1 and the front label gets filled.
The problem for me is the back cover that should take the right track nr, title and length from several rows (VLOOKUP uses only one row at a time)? How to make it work, that excel would know that on the first album there are 3 tracks and when chaging a number in C1 there would be 2 tracks on a second one.