I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
I found a reference on Ozgrid.com that allows me to sum values located every 2 columns. My need now is to confirm how to do that for even and odd columns:
I am compiling odds for football matches using the last 16 match outcome data for each team. Basically I need a solution or formula that counts the number of home wins, away wins and draws for each team in the last 16. This would basically mean allowing for the latest match outcome added into and then eliminating the 17th match back so that formulas from the last 16 matches only are always calculated. I envisage having a the data in a row of of 16 columns for each eam.
I've found myself having to use a spreadsheet to calculate some acoustics problems, (problematic frequencies, called modes, in orders ranging from 0,1,2,3....).
Ideally what I want to be able to do is simply to get Excell to calculate all the possible combinations of these modes.
A combination could be: 000 011 101 110 111 012 143 etc.
Obviously this is a nightmare to input by hand, is there any kind of function I can use to help with this process?
Also these frequencies only need to be calculated up to a certain point, so is it possible I could also instruct the cell to only display a value if it is less than a value in another cell?
Create a random selection based on odds. It would be similar to the RANDBETWEEN function, but in this case I specifically want one of the three values to be selected randomly in cell C2 based on the odds given.
When exporting a set of historical sports results to Excel 2003 the numeric odds are converted to a date eg 7/2 changes to 07-Feb. Can this be prevented or adjusted to retain the original data.
Also if I may ask can the UK style odds be converted to a decimal style. Eg. 7/2 (UK) needs to be converted to 4.5 ( Decimal equivalent )
I am a teacher and have a recurring problem when trying to sort data in excel. The problem involves national curriculum sub levels. For those not familiar with these levels, there is a main level then 3 sub levels. The problem is that they are counter intuative to the way excel sorts data. The sub levels work like this:
4a - highest 4b 4c 3a 3b 3c 2a 2b 2c - lowest
There are lower and higher levels, but you get the idea. If I have a set of pupil data that I want to sort into decending order, I find it difficult in Excel. E.g.
At the moment I manually complete this. E.g. for Mary the Sub levels progress required would be 4, as there are 4 sub levels between 3b and 4a. For Joe it would be 3 as there are 3 sub levels between 3a and 4a, etc. Is there a quick way to get excel to calculate this.
I would like to randomly pick a name based on percentage. In the example below Deb takes up 50% of the wheel, if you were to spin the wheel she would have the highest chance of landing on the dial. How you could make excel randomly pick a name based these odds. The list will have over 50 names and each name will be earning points throughout the year. The more points you have the higher percentage of the pie you get and the higher your odds off getting picked in the lottery.
I have a line graph which I use in conjunction with live horseracing odds. On the vertical axis are the odds which are sourced from a worksheet. Each horse is represented by a data series that moves along the horizontal axis. The refresh rate is determined by the user embedded in the code.
I would like to expand the length of each data change along the horizontal axis as the data can be volatile. Sometimes there are rapid changes on the vertical axis which makes it difficult to interpret. With each refresh the line data moves about 3mm. It would be much better if it were about 5mm, especially as I now use a widescreen display.
There are no values on the horizontal axis as such, only time is suppose set by Excel?
Sheet 2, Row 1 column B-E contain restraurant names (only on the top row, like field names).. i.e. I manually put the date in because typically the projected date is different from the actual review date.
-A----------- B ----------------C ------D-------- E----- Date |Ruby Tuesdays|Olive Garden|TGIF|Ruths Chris|
I need the data from Sheet 1 Column B moved to sheet 2 in the next open row (i currently have data in row 1..the field names and down to row 35). This will be continuous so each time i need it to add the score as a new row in the correct field (restaurant name), IF the restaurant isnt listed, I want a new field named with the restaurant name and then place the score in the correct row and column. So, in the example I'd need Outback added.
I have the following macro that imports a copy of data into a worksheet. I would like it to paste the data below in first empty row (where column A is empty) instead of A1. I am using Excel 2010.
Copy and paste the data from different excel sheets to one main sheet.
I will get a file which may contain 10 sheets or 15 sheets depends on data and rows also not sure it varies from sheet to sheet. Now, I would like to consolidate all the sheet data except first row (heading) for first instance I need heading and second sheet onwards not required the headings hence, macro should copy from second row onwards.
The data should get paste one by one if first sheet contains rows till A100 then in main sheet for second sheet data paste should happen from A101.
I have an Excel worksheet with potentially 1000+ rows of data.
Create a way where I select something (a macro button ?) for that individual row of Worksheet1 -- and it takes selected cell data from that row and inserts it into specified cells in another worksheet (Worksheet2) within that same workbook.
Also, this Excel data (of Worksheet1) will be sorted every week - so the ?macro button? that is used needs to follow the same row when a sort is performed.
I can do this by creating a macro button, but would have to create an individual macro button for every individual row of data. (That's 1000+ macro buttons to create !?!)
Is there better way of doing this with a macro button OR is there a completely different/better way to accomplish the same thing ?
Spreadsheet 1 has 10 columns of data (A-J). I want to copy a variable number of rows from spreadsheet 1 to spreadsheet 2.
When I paste into spreadsheet 2, I'd like to automatically insert blank cells in three places, taking the total number of columns to 13. I'd like columns C, F and I to be blank, and the last column with data to be M.
I will perform this task regularly, and add the copied cells to the bottom of spreadsheet 2, so I'd only like to insert blank cells within the range that I'm copying, not the entire spreadsheet.
I will then populate the blank cells with a VLOOKUP function. Do I need another macro to automatically add the formula to the cells, or is there a way to include this in the cell-inserting macro?
I have a chart and a data table (please see attached file named "delete_1.xls).
I need to present these 2 items in a single slide of PowerPoint.
I have done like this.
-First copied the chart from Excel. -Then pasted it in PowerPoint using "Paste special". -Then clicked "picture (windows metafile)" this is to reduce memory consumption
Then repeated the above steps for the excel table too.
The PowerPoint slide that I got cannot be attached as system doesn't allow me.
Question: I wanted to a give a paper copy of the PowerPoint slide to my Director who needs a PowerPoint slide and not an Excel chart. But in the slide, the numbers of the table look much juggled and as if the numbers are too closely typed.
I tried with various different fonts in Excel and then copy and pasted in PowerPoint but the problem persists.
What things I should do in Excel table so that cell values are clear in the Table presented in PowerPoint ?
Column A = Date Column B = Value 1 Column C = Value 2
I have a folder with excel files named by date (e.g. 081212 = 8th December 12). Within each of those files is Value 1 and Value 2. They are always in the same cell (B6= Value 1, B16= Value 2).
How can i create a macro/script where the Values in Column B and Column C in my spreadsheet are automatically updated where there is a Date in Column A but no values in Column B or C?
In other words, i need the script to read the date in Column A and if Column B and Column C are blank, then it needs to find the corresponding date excel file and copy Value 1 and Value 2 into the cells in my spreadsheet.
I'm trying to use the Calculate() event in VBA, I have a column containing formulas but I would like the values that it displays to be real values, like when I special paste them as values, I do know how to do this in the Change() event with manual input but these are changed depending on other things. I tried a couple of things but none of them really worked.. This is what I have until now:
Private Sub Worksheet_Calculate() Application.EnableEvents = False Application.ScreenUpdating = False For Each Cell In Range("R8:R1169") Cell.Copy Cell.Offset(-11, 0).PasteSpecial Paste:=xlPasteValuesxl, Operation:=xlNone Next Cell Application.EnableEvents = True Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
I'm stuck on the final piece of my macro project. I've attached a workbook with two sheets: sheet 1 is what I currently have, and sheet 2 is what I'd like my report to look like when complete.
On Sheet 1: - column B is called Supervisor Name - Columns D-O are months of the year, with either a Yes or No in each cell.
I'd like to write a macro that will: - Take all unique values in Supervisor Name column, and paste these Above the current table. - For each Supervisor, and each month, I'd like it to calculate, as a %, the number of Yes mentions in each month divided by the total cells (Yes/(Yes+No)).
Sheet 2 contains the output, in the format I'd like to see it.
Sheet 2: Supervisor Name October November December
keep getting "The formula you typed contains an error".
To calculate a person (or anything else) age you basically subtract his date of birth from today's date. However which date functions to use depends on the amount of precision and the output format you want.
3. If we want his age in years and months, then we can use a formula like this one: =INT((TODAY()-A1)/365.25) & ” years and ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months”
The output of this formula will be as follows: 40 years and 2 months.
I change A1 to G3 because that is where I the birth date.
MyCalc:=CALCULATE([MyMeasure], Filter(Tbl,Tbl[Field] && Tbl2,Tbl2[Field2])) It works with 1 Filter : MyCalc:=CALCULATE([MyMeasure], Filter(Tbl,Tbl[Field])) Just falls down when I add the second filter.
Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.
I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008
All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'
Sub Create_formula_result()
Dim Limit As Long Dim r As range Set r = range("A1")