My spread sheet reads column A teamname, B games played, C wins, D Losses, E Ties, F winning % and G GAMES BACK
I'm looking for a formula to determine games back based on wins, losses, ties and also based on the number of games played.
example, team 1 played 9 games and is 8-0-1. Team 2 has played 9 games and is 8-1-0. Team 2 is would be a 1/2 game back. Team three is 8-0-0 also a 1/2 game behind team 1 but tied with team 2.
So in column G it would read team1 "-", Team 2 ".5" and team 3 ".5"
I am making a spreadsheet for my fantasy baseball league and I have it set up how I want it, minus the correct formula(s) to make it work.
Basically, I have 15 different tabs in one .xlsx file. The first 14 are each team and their players. The 15th is a huge ranking list of all the players in the league basically.
What I want to happen is, as I enter a name in any of the first 14 tabs, somehow on the 15th the corresponding name with be crossed out, colored, etc.
I have a spreadsheet that can't be password protected but this doesn't mean just anyone should be accessing it. Is there a way to record who opens it and when? Ideally I could place some code into the spreadsheet that recorded on a sheet who has opened it and when. This will detract certain nosy people from accessing it!
I am in sales and we currently have a excel spreadsheet with call tracking and a sales tracking on it. I would like something a little more inviting. We have quotas that we have to meet. 100 calls a month, 20 quotes to put out, $11k is sales.
I was thinking maybe something that was broke down by week for calls. The sales and quotes, have a graph associated with them. When we type in the dollar amount it will raise the graph that much. I think this will make it easier to manage. I have attached what our call tracking looks like now.
I am currently producing a work number tracking spreadsheet for the people I work with and wanted to know if there was a way to generate a pop up window that created fields to enter the data for the job being inputted.
I currently have it just a simple macro (below) that enters a new line, adds a sequential number and adds both the date entered, as well as an expected finishing date. It does what I want it to do, however I am looking for it to be fool-proof as the last system was broken as people didn't fill it in fully.
Code: Sub New_Line() Range("A4").EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow Range("A4").Value = Range("A5").Value + 1 Range("C4").Value = Now() Range("D4").Value = DateSerial(Year(Now()), Month(Now()) + 1, Day(Now() - 10)) End Sub
On the previous system it used a worksheet that did similar, with dropdown menus that auto-filled the sheet as well as inputted into the spreadsheet. That was produced by an old employee that isn't working on it any more.
I have a spreadsheet that is being used for tracking work completed each day in a week. Each day has 5 columns and 10 rows of data to potentially be entered, some of the data is text and some is numerical. Can anyone help me come up with a formula that will count the text and the numerical entries for each day? I tried to use a nested if statement but it exceeded the number of nestings available. The range of cells for the first group is B6:F16.
As a side question, will count work for a range of data or only one column or row at a time?
I'm trying to produce a spreadsheet for tracking pupil's progress through a year at school (Targets/Predictions etc) but am having some problems with creating a summary of data gathered for each pupil. I have attached the file for your perusal; On the data entry sheet staff will fill in the appropriate data, and on the summary sheet I am looking to generate summaries for each pupil (I have set up how I want it to look). This may sound easy, (and probably is); the issue is that there are around 30 subjects in total, but pupils will only have be doing 5 of them, I need the summary sheet to show the information for subjects they are taking only (It should come up with the subject name under the headings subject1/2 etc and the appropriate grades to go with them, missing the blanks out. Obviously I could do this by cutting and pasting for each pupil...But there will be almost 2000 pupils in the list!!!
I'm struggling to find a way to index data in an array that meets certain matching criteria. I am looking for an employee's rate on a given day by searching a database that lists the dates that an employee's rate was changed. I was hoping to solve it with a crafty index and match array formula but have been unable to find something that works so far. I have attached a simplified example of what I am trying to do.
I am tracking baseball batting statistics. The first column in each row is the player's name and the next column is the date. Several columns of statistics follow. I enter the batting stats game by game. I know I can use the SUBTOTAL function combined with AutoFilter to view each player's statistics individually totalled, but what I'd like is to see that subtotal line permanently displayed for each player.
I want to be able to open the Excel spreadsheet, and have some easy way to post formulas into that document. I was thinking a Macro would work, but I cannot seem to find a way. Columns A through V contain the data that is exported from the payroll system. I want to find a way to paste my formulas into W2:AH200. The formulas will always be the same.
I'd like the user to be able to:
1. Export the payroll information to Excel and open the document. 2. Run a macro (or some similar action) to place the formulas in W2:AH200 into the spreadsheet. 3. Input data into AH2, AH3, AH4, and AH5.
A sample document is attached. Columns A-V are what get exported by the payroll system, and W-AH are my formulas I've added to this sample (and what I want to be able to easily add to new spreadsheets).
For various reasons, I need to determine how many total calculations are being performed in a spreadsheet. These can be IF statements, sum, max, vlookups etc but I am looking to determine the quantity. I do not need to know how many of each just how many in total.
Is there a relatively simple method of conducting this search? I don't mind VBA code, etc.
my query is about excel formulaes, I always get stuck with them, not sure how to complete them, as I need to keep a record of marks tally in a spreadsheet. I have been keeping marks record in the old tabulated manner.
how to highlight entire rows within a spreedsheet based on the information in a cell. I have gone to the conditional formatting and done:
=$A4="Needs Labs" and formatted that red, but when i go to note what areas it applies to, It will not highlight the row. I use the wizard box to decide where to apply the formatting like I saw on an online tutorial and dragged across the row, but nothing happened.
I've attached a sample document of my data layout. On the Compare tab, I'd like to build a sumif formula that takes the data in column B and uses it to reference the other tabs without having to use a large nested IF statement. For example, on the Compare tab, in cell C2, the formula will know to look at the "JAN MONTH" tab because B2 says "JAN", then the rest of the sumif would be looking at the data in column A and matching it with the data in column A in the correct tab.
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.
I have a series of packages that are denoted by text phrases. Examples:
4/6/12 2/12/12 6/4/12 18/12 24/12
For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).
I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
I am looking to track changes on a "Log" sheet throughout an entire workbook. The following code (found here and given below) will give me:
The cell . The date/time it was changed . What the new content in that cell is
The two points I would like it to include, in order of importance, is: The name of the worksheet that cell exists in. The user that made that change*Bonus points* a hyperlink that'll bring me to the worksheet that was edited.
VB: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim r As Long, OutSht As Worksheet Application.EnableEvents = False Set OutSht = Sheets("Log")
I have a worksheet that has multiple units listed on it. Each unit has a materials used list with quantities. I want to reference this column to another sheet and get a total materials needed list and also reference it to my inventory and subtract that number from my materials on hand. how can I do this?
So far I was able to find solutions to all of my small problems with excel just by searching here but with this one I need help and ideas how can it be done differently.
We need to track inventory from when its taken, when it was installed and we need to know every month when inventory is counted how much do we have on hand in our truck....
I'd like to make a vba macro in excel which will be just like "Track changes" feature. I'd like to track changed cells and when and who did it. (Date, User, Cell, Last value, New value). It is important to make a report in separate worksheet and automatically fill this worksheet when the user save changes.
Aaron Blood has created an excellent DDE Change Tracking.
The file is in the download area of [url]
The macro, in the xls file, archive the data from top (a1) to the End of the worksheet.
I want invert the way to file/archive the data… copying all the cells already filled, a cell down and filing the top ( A1...) with the last DDE value (In the TracK sheets).
How can I do that?
The primary code is:
Sub Change_Tracker(Watch As String, TrackOn As String)
Dim NextCell As Range, r As Double, c As Double
With Worksheets(TrackOn) r = .Rows.Count Set NextCell = .Cells(r, 1).End(xlUp).Offset(1, 0) End With
I manage a small motel with 6 rooms and I'm trying to write a simple program to track my customer reservations including their details. All the commercial software is too elaborate and way out of my price range. I'm slowly learning excel but this has proved beyond my capabilities.
I want to create a spreadsheet that I can export my transactions from my credit card onto -- is there a way to make it so that the transactions that haven't been covered by my most recent payment(s) are red, while the ones that are paid are green without manually going through & doing it? I know there's the IF, TRUE, FALSE formulas, but I'm confused on how to use them.
Basically, if I spend $1,000 between 5 transactions and make a $400 payment, I want the oldest transactions totalling up to $400 to turn green, while the remaining are stay red until a new payment is posted.
I have a spreadsheet i would like to have changes tracked to automatically. I know it's only a matter of clicking a few buttons to get this to happen, but i was wondering if there is a way to have excel do this by default when i open the file. I would also like to have the changes highlighted automatically.
I want to type status update for an action - then when I press enter it should automatically as soon as I press enter:
- cut the text I typed from the cell leaving it blank - move to the target cell (its in the same row) and "add" that text string (with a date stamp added) to the text already in the target cell.
Every day I type an update against the action in the first cell - and I press enter - it should copy that text into the target cell.
That way I have a complete history of updates against the action with date stamps.
Sounds pretty simple but I don't know where to start.
Example of a simple worksheet attached : Tracker.xls
I want column J to reflect the total amount of all trades which were open at the time the trade on the row is closed.
(the data is sorted according to closed trade times).
So while the trade on row 4 is the first trade to close, there were other trades also open prior to the closing. I need to show the total amount in column E of all trades which were open during the time window that the trade on row 4 was open.
I am using excel as point of sale book (to record customer name, service, and total price etc.) at our hair salon. We have employees that may be there to manage alone from time to time, and enter clients into excel. Our worry is straight forward, and involves them erasing what they wrote. I am confident that the actual service and price is entered at the beginning, but want to track their changes to their own entries.
The "track changes" would work if it "tracked changes" after entry. But it seems to track the last change from saving. For example....the employee enters $40.25 presses enter--after she knows she can get away with a change, she may erase it altogether or change it to say $16.75.
I own a restaurant and have a inventory list. I track the price of a specific item in column E, and want to record the date i modify column E in column D. So, I change E5, I want D5 to say the date.
