Is it possible to turn a range of data into a more detailed table.
for instance -
the input data would be something like
1 - 3 @ 50%
4 - 6 @20%
8-10 @70%
the output i'm looking for would be a table like below
1 - 50%
2 - 50%
3 - 50%
4 - 20%
5 - 20%
6 - 20%
7 - 0
8 - 70%
9 - 70%
10 - 70%
I have 2 lines with pairs and 3rd with corresponding values, nee to construct a matrix out of it with formulas
Example
Need to turn
AAABBDBCDCDC0.30.20.130.550.840.43
into
ABCDA11-0.31-0.21-0.13B0.311-0.551-0.84C0.20.5510.43D0.130.841-0.431
So the one that stands above in the initial file goes to the top line in the matrix. 2 line -> column in matrix. In case there is no match need to seek reverse in 1,2 and reflect as 1-VALUE in the matrix
Is it possible to turn monthly data into daily? Repeating the same data for four sets of five working days to turn a CPI monthly inflation index into daily data, to turn example 1 into example 2.
Example 1.
DATEVALUE
2008-01-01 212.174
2008-02-01 212.687
2008-03-01 213.448
2008-04-01 213.942
2008-05-01 215.208
Example 2:
2008-01-01 212.174
2008-01-02 212.174
2008-01-03 212.174
2008-01-04 212.174
The format of the text in which I need to extract numbers is as follows:
23411268 - 23411270
Need to extract the following:
23411268
23411269
23411270
These numbers have to be listed in three seperate rows.
I have some data formatted as a table. There are some blank rows at the end that still contain formula.
Every time I sort the data these rows move to the beginning. I have tried only selecting the active rows of data, but the whole lot defaults in each time
How do I sort data with a selected range only (within a table)?
Don't really want a macro as this will need to be used by others
I have two data tables (Table1 and Table2) on two different sheets (Sheet 1 and Sheet 2). In Col1 of Table2, I have "copied" the values of Col1 in Table1 by using a simple equal (=) formula. Secondly, I have an autofilter on Table2 and a macro that automatically updates the filter when the sheet (Sheet 2) is selected.
My problem is that I would like Table2 to be more dyanmic, i.e. I don't want to change the range of the Table2 each time I add, or subtract, a value from Table1.
Could a OFFSET formula be employed in any way?
I have attached an example file.
The macro is:
[Code] .....
ExpandTableExample.xlsm
I have created Pivot Table in "Sheet2" and the source data is in "Report" tab. The source data is in the range of A1:K200 -means until the last cell of the excel. Now if the data is more then this we need to change the source data every time before we refresh the pivot table - Is there any macro or any way so the range can be change automatically ?
View 9 Replies View RelatedHow or if it is even possible to have a table automatically adjust its range or the number or rows it has based on another table
I have two tables:
Table1 is raw data that is added two daily
Table2 has formulas with column header references to Table1 that processes the information and converts it to a readable format. (basically barcodes to words)
Is there a way to get Table2 to automatically resize to the same number of rows that Table1 has whenever data is added??
Possible to get a pivot table to automatically expand the range selection of data as it grows. I have a worksheet that grows daily and I am running a pivot table from this. If I refresh the pivot table it doesn't pick up new data unless i change the range or i have a huge range selected in the first place. Problem is if i select a huge range then the grouping options i am using won't work correctly.
View 5 Replies View RelatedI have a problem whereby I have a list of data that has a date, a transaction and a balance. I want to be able to rearrange this data into multiple tables, one for each month.
The pictures below explain the situation better, on the left is the list of data I need to sort, and on the right is how I'd like the data displayed. So for the May columns I would like to display all transactions that happened in May and, depending on whether it is an income or an expense, the amount in the corresponding column.
i.e. so the finish result looks something like this:
I have a report I am attempting to populate with data from a pivot table in another worksheet. Column A holds all the reference numbers (primary key), column B contains various start dates, and I want column C to contain all the payments made since the start date for each reference number.
The source data is a pivot table with Row = Reference number, column = transaction date, values = transaction amounts. This is an extremely large table, as I'm processing data from almost 1,200 cases, which each have around 20 payments spread over the last year, on completely random days. What I would like to do is build a formula in my report which looks up the records for the reference number from column A, and then adds up all the payments which have been made after the date in column B (and ignore any payments in the table which are before that date).
And to make things more complicated:
if an error is generated, it needs to return as 0, not #N/AThe report has the dates in UK format dd/mm/yyyy, but the pivot table has the dates in SQL format: yyyy-mm-ddThe pivot table is connected to a SQL database via ODBC and has to refresh every time it is opened.
=IF(ISERROR(VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE)),0,VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE))
How to convert "Excel table" to a range without loosing data connection to access?
View 2 Replies View RelatedI have a user that uses pivot tables and charts every month to do a report. He wants to copy his charts every month and then just change the month in the data for the pivot table, but not matter what he tries its tied to the previous month and then chart data range in the select data source properties is grayed out. how to get this to work?
View 3 Replies View Relatedhow to find the source data range of a pivot table (that already exists) in Excel 2007?
View 1 Replies View RelatedI have the following code to update a pivot table:
Code:
Dim pt As PivotTable
Application.EnableCancelKey = xlDisabled
For Each pt In ActiveWorkbook.Worksheets("sheet1").PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="source!R4C1:R33443C55" _
, Version:=xlPivotTableVersion10)
Next pt
the R33443 term is what will be changing, the columns and the starting row should stay the same. is there a way to instead of using R33443, to enable the range to be changed based on a cell value?
I have this annoying problem with my excel 2003. And i have no clue at all how to solve this.
Each time i write 2.5 it turns into 02.may. Does any one know how i can fix this?
I fill out this form everyday and sometimes need to type "S" and every time it fills in "Sampling Rate #1" because that's what it says in the same column higher up. Is it possible to turn this off somehow?
View 3 Replies View RelatedI want to turn on (tick) a check box from within a macro.
I have a sales and prospects spreadsheet. It has information in columns A to O. The last 3 columns M, N and O, require a 'Y' for Yes or 'N' for No entry. If entering N, the background colour of the cell turns grey using conditional formatting, and if entering Y the background colour of the cell turns Gold using conditional formatting. Column O is headed 'Sold', so that column is onviously the final Y. Upon entering Y in column O, ie the sale is made, I's like it that the whole row, so all cells of that row in columns A to and including O turn gold to easily visually differentiate done deals from prospects.
Also what would be cool is if when a deal is done, it not only turns the whole row gold, but moves the row to the top of the sheet, so by dividing done deals from prospects, that is utopia at this stage though, more important is turning the row gold upon a Y entry in column O.
I recently installed Office 2002 onto a new computer and found that whenever I drag-and-drop any amount of data from one cell to another, a loud obnoxious noise plays when I release the drag-and-drop data.
I was using the same version of Office on my old computer and did not have this problem.
I'm guessing it's a clipboard issue, since when I open the Excel clipboard (where I currently have all options as unchecked) and copy something so it is captured there, the same noise plays.
I already have turned off Windows sounds via the Control Panel and have no clue how to turn off this sound.
I'm going through financial statements and I would like to be able to run full blown analysis on them. Most public financial reports are written so that a lot of zero's do not take up the useful space. As such (and there is a ton of this solution out there I've found), many people want to take $1,500,000,000 and turn it into 1.5M
I however, do not. I want to convert it backwards. So when something says 5.19 I would like to format it in such a way that it will read (and I can run calculations against it) as 5,190,000,000 (there are no alpha characters, there are instances where there will be negative numbers). Then I could copy this to the 17 different financial statements I've already got rock'n rolling in Excel right now.
I have the Macro
[Code]....
That starts a timer macro (Recalc) in my excel sheet when the workbook is opened, my issue is that when the work book closes it continues to run.
Will the following macro fix this problem, or is there some command like Workbook_Open() but for closing?
[Code] ....
I have a workbook that has many Pictures created with the Camera command. I have found that VBA macros in any workbook open at the same time as the one with Pictures run very slowly. Is there a way of temporarily turning off the Picture Refresh within the VBA in the same way as you can turn off automatic formula updating.
Keith
Current Tabel:
Sheet1
ABCDEFGHIJKLMNO1EVENT NAME DATEGROUP / LOGOEVENT LOCATION DELIVERY DATEITEMCountMUGSBLK T'SPADSGREY T'S DRIVEBLK PENSCOINSBLK POLOS35Inter.05/01/12ABCShip to ABC04/25/12 60 45 32
This is what I want to do with it:
Sheet1
ABCDEFG1EVENT NAME DATEGROUP / LOGOEVENT LOCATION DELIVERY DATEITEMCount35Inter.05/01/12ABCShip to ABC04/25/12MUGS6036Inter.05/01/12ABCShip to ABC04/25/12BLK T'S 37Inter.05/01/12ABCShip to ABC04/25/12PADS4538Inter.05/01/12ABCShip to ABC04/25/12 DRIVE3239Inter.05/01/12ABCShip to ABC04/25/12GREY T'S 40Inter.05/01/12ABCShip to ABC04/25/12BLK PENS 41Inter.05/01/12ABCShip to ABC04/25/12COINS 42Inter.05/01/12ABCShip to ABC04/25/12BLK POLOS
How to stop my 100% from changing to 1000% after it pastes?
Here's the pc of code that is performing the copy/paste from one sheet to another sheet within the same workbook..
Code:
With Sheets("BOM Worksheet").Range("C" & nr)
.Value = c.Offset(, -16).Value
.Font.FontStyle = "Bold"
.Font.Color = RGB(186, 85, 211)
End With
I have a column of dates and have been asked to turn a cell next to it green, orange or red but I don't think this can be done?
Otherwise is there a way to turn the background of the cell a different colour, am sure there is?
What I need is if for example the date in the column is 01.08.08 then up until 7 days I want it to turn green, between 8-10 days I want it to go orange and over 10 days it needs to go red. Is this possible?
I need to turn a list of information into a true spreadsheet, how do I look up the information to fall under the appropriate headers?
Example:
Instead of...
Invoice # 000
Date Of invoice 01/01/08
Name: Smith/Steve
Invoice # 001
Date of invoice 01/01/08
Name: Smith/Sally
I need...
Invoice Date of invoice Name
000 01/01/08 Smith/Steve
001 01/01/08 Smith/Sally
"Decision Date". It is a numerical date (ex: 1/5/09 indicating January 5th 2009).
I need to turn that date into the week that it falls into within a particular quarter of a year.
1/5/09 would be Week 2 (it is in the second week of January, and 2nd week of the quarter)
2/3/09 would be week 6 (6th week of the quarter).
And then I need the formula to start over once each quarter restarts... for example, April 1st would be week 1 (1st week of the second quarter).
I have this formula:
=IF('2010'!R10C2="y",'2010'!R10C3,"")
And I want to be able to drag it down and have the cells update, but all it does is keep the same values.
Is there something I need to turn on or off.
i would like to know how to turn the word beside into numbers, ie if b=2,e=5,s=60,i=10,d=4,
also can this word be in the one cell or would it have to be entered separately, ie one letter per cell??