# Formula- Making A 'sports Day' Spreadsheet For A Project

Aug 28, 2007

I am making a 'sports day' spreadsheet for a project and i have one problem, the spread sheet is meant to have the possiblitlity of mistakes reduced to a minimum.

I have 6 teams in the sports day and under each i put 1st, 2nd,3rd,4th,5th,6th.

[url]

basically i need the rows to only accept 1st 2nd 3rd..... only once. I need something like an error message to come up if a value is entered twice on a row.

## Converting Rankings To Scores For Sports Spreadsheet

Jul 4, 2014

How I can use a formula for inputting a persons position in a race for the scores appear automatically in the adjacent column. E.g 1st place 10 pts, 2nd 8pts etc?

## Spreadsheet To Manage My Players In A Virtual Sports Game

Dec 13, 2007

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.

For example:

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.

## Amend Ranking Formula For Sports Results

Oct 15, 2009

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:

=IF(AL44="","",RANK(AL44,AL44:AL53)&CHOOSE(AND(RANK(AL44,AL44:AL53)<>{11,12,13})*MIN(4,MOD(RANK(AL44 ,AL44:AL53),10))+1,"th","st","nd","rd","th"))

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.

Mar 28, 2014

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.

## IF/VLOOKUP (set Up A Spreadsheet Which Automatically Calculates The Start And End Dates Of Project Tasks)

Feb 1, 2009

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.

## Making Copies Of Spreadsheet Containing Macros?

Aug 28, 2013

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?

## Making A Spreadsheet Smaller On Opening

May 16, 2007

I have a 30 in monitor (I know I'm lucky!) but when I open a new spreadsheet it opens all the way across the screen 100 columns and 100 rows. How can I change the size when it opens?

## Generating List Of Project Parts Based On Project Duration And Time Window

Aug 4, 2014

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.

## Consolidate 4 Excel Project Lists (Workbooks) To New Master Project List Using VBA

Sep 5, 2013

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

[code]....

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 Number

Project Description
...
1111E.000000001

[code]....

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.

## Get Excel To Open MS Project File And Then Run Project Macro On It

Jul 30, 2013

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

[code].....

## Protect VBA Project Or Lock Project For Viewing By VBA

Apr 27, 2007

I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.

## Sports Record

Mar 7, 2007

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

## Vba Formula Automatically Inserts Into The MS Project Box

Jan 9, 2009

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.

## Looking Up Sports Stats From Web Data

Jun 22, 2009

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 will attach the sheets.

## Creating A Sports Table

Nov 16, 2009

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...)

The games will be played as a best of three stets so there will be no draws.

## Sports Team W/L Counter.

Jan 8, 2010

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 .......................

## Tally Report Of X: Count On Column B (project \$) Depending On The Criteria On Column A (project Description)

Aug 28, 2007

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].

## Formula To Have Spreadsheet 2 Pick Items From Pass / Fail Column On Spreadsheet 1

Jan 23, 2012

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?

## Converting Excel Formula To MS Project Custom Field?

Mar 20, 2014

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.)

Formula where A1 is the Start Date:

Code:
=IF(AND(A1>=DATE(2014,2,1),A1=DATE(2014,5,3),A1=DATE(2014,8,2),A1=DATE(2014,11,1),A1

## Multi-Spreadsheet Formula Down To Populate The Other Cells In The Total Spreadsheet

Jan 4, 2010

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.

## Formula To Show Project Size Based On Number Of Hours

Jul 8, 2009

I need to show the size of a project in a cell based on it's number of hours, e.g.

Between 5-80 hours = SMALL
Between 80-200 hours = MEDIUM
Over 200 hours = LARGE

How can I do this? I can't get the IF function to work like this (unless I'm doing something wrong) and I thought about using a pivot table but think there must be an easy formula to use?

## Formula On Making A Statement

Jan 20, 2014

I need a formula to look at a cell and if it is =< 102 say "Flat Bed", or =120 "Double Drop"

## Making A Formula Static

Jul 13, 2008

I have 2 sheets in a work book. In the first sheet in A27 I have a value. In the secend sheet I have a formula "=SHEET1!\$A\$27".

Problem is when I insert a new row in sheet 1 the formula in sheet 2 changes to "=SHEET1\$A\$28" and I want the formula in sheet 2 to stay the same that is, always =SHEET1!\$A\$27.

## Making An Exception Formula

May 17, 2006

I am trying to get 2 different formulas to run depending on the value of the 2 cells.
Example: if cell "A" is equal to cell "B" then one formula, if it is different than a second formula.

I hope I did this right, if not please let me know and I will ask the question in a different manner.

## Making HLookup Formula Dynamic?

Jun 20, 2014

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?

## Making A Cell With A Formula Appear Empty

Mar 16, 2009

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?

## Making Formula Calculate At The Top Of Hour

Apr 19, 2014

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.

## Making Dropdown Menu That Will Change Formula?

Dec 10, 2013

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

## Making A Formula To Automatically Fill A Label

Jun 8, 2009

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.