I would like to have a running percentage calculated in cell P3 for the attached file. Cells C3, E3, G3, I3, K3, M3 show the number of correct picks. Cells D3, F3, H3, J3, L3, N3 multiply the number of correct picks by a given value in row 1. For each round there are a possible 32 points. I would like to use some sort of IF Statement to have a running winning percentage calculated for each round where points are achieved.
Example:
Round 1 - #picks correct / 32 possible points
Round 2 - # picks correct from Round 1 + # picks correct Round 2 / 64 possible points
Round 3 - so on until round 6 with total picks correct overall / 192 possible points
Have list of employees and their dependents organized into the following columns: Employee ID (unique to employee), Relationship Code (E=employee, S=Spouse, D=child), andBirthDate. Would like to add a column called Sequence Code which counts the family members (employee= 0, S=1, oldest D=2, next oldest D = 3, and so on).
Employee ID RelationshipCode BirthDate Sequence Code 100006641 E 2/5/75 0 100006641 S 1/2/75 1 100006641 D 3/9/89 2 100005555 E 2/5/77 0 100005555 D 1/2/89 2 100005555 D 3/9/88 1
Note that the list is sorted by Employee ID, but not Birthdate. Can this be done with a formula in the Sequence Code column? I really suck at VBA
I was wanting to put it on a spreadsheet. Probably a pretty easy deal, but I don't know the formula to use. this is what I want: Joes start weight 200 minus week ones weight, then the original 200 minus week 2's weight and so on till week 8. I want to keep a running total of weight lost in pounds as well as percentage.
I need to dynamically build a formula for calculating a running percentage for a row of data. The columns that are referenced by the formulas are static (f thru r), but the row number can change depending the number of rows of data. I have a set of data that can have one to many lines in it (the data is placed on the worksheet using vba code). After the data is placed, a total line is built using this
'Find row number for total row (2 rows down from last row of data) endrow = Range("a35").End(xlDown).Row sumrow = endrow + 2
'Set sum formula for the "e" column Range("e" & sumrow).Formula = "=sum(e35:e" & endrow & ")"
'copy total formulas to columns f thru r...............................
I've got data being scraped from a site, putting 1 new workbook in a folder each day
each workbook has 40 sheets in it.
i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.
ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..
is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?
I would like to know if it is possible to have a cell calculate a running total based on previous data as soon as you enter new information. The spreadsheet is a log of suspensions. Relevant columns include the student's name, date, and length of suspension (in days). For example, if Column A= Name (could be separated into first and last name if necessary, or merged into one column), B= Date, C=# of Days, D= Reason and so on, can Excel add the number of total days of suspension for all violations as soon as a new record is created? (If a student has already been suspended for one day, then gets suspended a month later for something else for three days, can excel tell you immediately how many days that student has been suspended total?)
This is actually a question from my mom, who asked me to help her, and who told me that a former coworker's wife had created a spreadsheet that could do this. Unfortunately, that spreadsheet no longer exists and they must recreate something.
What I want to do is - Column H shows my current position, and column L shows the p&l.
I would like excel to calculate the p&l for each position, and put the results in column M.
For example: H3 is the opening of a new position, as H2 shows the current position as zero, and H4 is not equal to zero. The position was added to in H4, and I'm now holding 2. The entire position was sold in H5, and my current position is back to zero. I would like excel to then show in M5, the sum of H3,H4 and H5, and then start the calculation again, and keep doing so down the entire column. If there was only 3 p&l cells for each position, it wouldn't have been a problem for me to get it done, but unfortunately, they can vary quite a bit.
Have a sample file attached, and what I want to do is as follows-
Column H shows my current position, and column L shows the p&l.
I would like excel to calculate the p&l for each position, and put the results in column M.
For example,
H3 is the opening of a new position, as H2 shows the current position as zero, and H4 is not equal to zero. The position was added to in H4, and I'm now holding 2. The entire position was sold in H5, and my current position is back to zero. I would like excel to then show in M5, the sum of H3,H4 and H5, and then start the calculation again, and keep doing so down the entire column. If there was only 3 p&l cells for each position, it wouldn't have been a problem for me to get it done, but unfortunately, they can vary quite a bit, and I have no idea how to do it
I have a list of group ID codes, which contain 1 or more product codes within them.
Some product codes contain an "alternative" value (the actual value is irrelevant) and others do not. See example below
Group ID Product Code Alternative
56381 240027 160380
[Code] .....
Ultimately what I need to achieve is a percentage of how many product codes, within each group ID, contain an alternative.
So for the example above, group ID 56381 would have 50% codes with alternatives.
By counting the occurrences of a group ID and whether 1 particular code has an alternative I have calculated individual percentages against the size of the group e.g. product code 240027 (given a value of 1) divided by the total number of codes in the group (4), returns 0.25. Obviously doing this across the whole group would give my result (0.5 or 50%)
However some groups contain over 100 codes and the spreadsheet is 40K rows!
I have a bunch of cells that use a drop down menu validation from a different set of cells so
Legend (this are in cells $A$7:$A$11) NA - Not Applicable 0 - Not attempted 1 - Not Yet Competant 2 - Satisfactory 3 - Exceedes Expectations
Other cells have Data Validation using Allow List =$A$7:$A$11 to make the drop down menu option appear. What I would like to do is add a 'hidden number' on cells A8:A11 (A8=0, A9=1, A10=2, A11=3) A7, being NA isn't included in the formula I want to set up.
In cells later on I'd like to make a formula that takes those 'hidden numbers' into account and works out an average. So 'hidden number' / 'potential total' and make the cell display as a percentage, the 'potential total would have to take into consideration that a value has been set, but not use that value, and times the number of values set by three.
I am trying to solve a problem. I am currently using this formula = SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel MARKETING 200612 -10 MARKETING 200701 -25 MARKETING 200708 -50 ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
I want to set up an automatic sensitivity analysis such as the one shown in example.doc attached, using my model that is attached (inventory model. zip). Going horizontally in the sensitivity analysis, I want to show 2H (3Q and 4Q) Sales Growth (30%, 35, 40,45,50,55,60%) and vertically I want to show 2Q Inventory Growth (50%, 55%, 60%, 65%, 70%, 75%, 80%) and I want the inside data to return sales/inventory spread ( multiple). Basically it would divide the dollar growth in sales in 2H by the dollar growth in inventory and return a multiple. Is it possible to set this up automatically using the data that I have here?
I am trying to run multiple applications. I can run one application i.e.
Application.Run (Sheets("Sheet2").Range("A8").Value) but not multiple i.e Application.Run (Sheets("Sheet2").Range("A8").Value) & Application.Run (Sheets("Sheet2").Range("A9").Value) at the same time.
I have also tried Application.Run (Sheets("Sheet2").Range("A8:A9").Value) but to no avail.
I have some code which I want to run on all apart from one sheet called 'List'. At the moment I am selecting each sheet before running code for the specified sheet. This doesn't allow for any sheet name changes etc. or additional sheets. Is there a quicker way of doing this rather than having a sub to manually select each sheet?
Here is an example of my code for a button on the sheet called 'List'. The only thing Sheet1, Sheet2 does in this code is select that sheet.
I am trying to run the same test on multiple cells, to get the column letters of cells while there are less than 78 columns in use (these column letters will later be used for the Range().Select work involving copy & paste-ing into a word document). I originally thought I could use GoTo statements in conjunction with variables but, having researched it and coming across this, I think I need to find another approach.
At present I have a long section of code that looks like this (I am writing and testing at present so what i make will be put into a larger macro):
VB: Sub FindColumnLetters() Dim i01Feb As String, i02March As String, i03April As String Dim NumbTemp As Integer, NumbTemp2 As Integer
[Code]....
However VBA didn't like the use of variables with the GoTo function and there are not decent ways around. how I can do the original but in less space? FYI the codes above are repeated for 3 columns, where I am actually doing this for ~20 column refs so the space it takes is HUGE at present.
I have the following code, I have it running to just work on Sheet 2 but I would like it to be able to run on mulitple sheets, not all but a range like from Sheet2:Sheet30.
I am running a spreadsheet to keep track of workshop technician times. The code I am using I have copied and modified from this forum works really well but I need the timer to go in minus time instead of stopping at 00:00:00. It work be really great if the timer cell changed colour it identify that the Technician had run over the allotted time.
I am seeking code that will go to Sheet 4 (named "Extract"), copy a1:a5 and return to the active cell the macro was launched from and copy the values in.
The challenge I am having is how to get the code to return back to the cell it was launched from since I want the flexibility to use this macro in multiple tabs/sheets to copy the info into ANY selected cell within ANY sheet. As it is now, I have to go change the sheet name each time for the then active sheet.
I've attempted to insert a generic worksheet reference, however I end up with a debug issue.
building a spreadsheet to track instructors' classes' test scores. I have 3 basic variables: 1.) The instructor's name 2.) The course taught, and 3.) The classes' scores for that particular course. The 3 courses each have 3 different variations of the end-of-course test (9- total tests.) On the last page of the excel spreadsheet, I have a block for each of the instructors. I've been trying (with no success) to keep a running average of each instructor's class test average for the 3 courses. The part that is stumping me is associating the instructor's name with their class average, and then averaging all of their grades on that page together; the main reason being that this document is living, and new grades are entered almost daily. I tried using variations of the count function with no luck, other than to tell me how many times a particular instructor has tested in a particular test. Please help, I have the feeling that the function I am looking for is way-above my skill-level in Excel.
how to run my Macro over multiple (about 60) .xlsx files. I have been looking around, but haven't quite got anything working yet.
I have all the files in the same folder with similar names (i.e. Subject_01.xlsx, Subject_02.xlsx, etc.).
What I'd like is:
*open Subject_01 *run script (some calculations, copy/pasting, my script for this is working fine) *save file under a different name (i.e. Subject_01_processed.xlsx) *close file *again for Subject_02, Subject_03, etc.
Additionally, I have a second question: after I have the processed .xlsx files, I'd like to copy some cells from each .xlsx file (e.g. A1-H1) to a new file. There, I'd like the data from Subject_01 to for example be in cells A1-H1, from Subject_02 in cells A2-H2, etc.
I need to run a very simple macro (filter by date, copy, and paste) on every worksheet in a workbook, and then place that data into a summary page at the "end" of the workbook. Most other posts had to do with some kind of formatting that would work in succession on the sheets linearly from left to right (for lack of a better way to put it). My problem, however, is that the data would need to be pasted into the summary worksheet, and then somehow the macro would come back to the next sheet (after the one it just did) and do it all over again, so on and so forth. The worksheets are arranged alphabetically, but other than that there is no sort of easily identifiable progression between them (i.e. Sheet1, Sheet 2, etc.).
I have 3 seperate macros (macro1, macro2, macro3) that have to be run in order. Is it possible to create a macro to run them in order and what would the code look like? I will have it assigned to a button on the worksheet.
I would like my macro to go to a certain folder- in this case N:Aexeo ClientsJabre2008Excel Diet Run, open each workbook therein ( to this end I have found the first part of the code below on another thread) and perform the ExcelDietMacro (also below). I am missing something though as nothing is happening, would anyone know from a quick glance what is wrong? Should I have these as 2 seperate Sub End Subs or combined into one? I know that the Excel Diet is correct thanks to the original designer and Rory on the forum for getting it adjusted to my needs.
Also each workbook within the folder will have the same password to open, is it possible to insert some code in the macro to do this automatically?
Sub LoopFiles() Dim MyFileName, MyPath As String Dim MyBook As Workbook MyPath = "N:Aexeo ClientsJabre2008Excel Diet Run" MyFileName = Dir(MyPath & "*.xls") Do Until MyFileName = "" Workbooks.Open MyPath & MyFileName Set MyBook = ActiveWorkbook Application.Run "ExcelDietMacro" MyBook.Save MyBook.Close MyFileName = Dir Loop End Sub
Sub ExcelDietMacro() ' ' ExcelDietMacro Macro.........................
I am trying to find a way to set up a macro that will allow me to pull in data - create the Pivot table - delete the table - then pull in fresh data (of a different row length - same number of columns) and create another Pivot table. I have tried to manipulate recorded code to no avail. Here is what I am starting with:
Const lngLastPossRow As Long = 65536 Range(Cells(1, 1), Cells(Cells(lngLastPossRow, 1).End(xlUp).Row, 24)).Name = "Data"
While it creates the first pivot table fine, after deleting it and starting again it wants to create the next sequence (PivotTable3) which crashes the macro. I must close the file and reopen to run it again.
