I was going through "To do list with progress tracker" template in Excel 2013. I noticed when i scroll down the page the heading column (A,B,C,D,E,F,G etc.) automatically matches with the inserted table headings.
View 1 RepliesI currently have a table with a range of headings (row & column), and the necessary data for it. On a new worksheet, I have a table with only a few of the headings, and I was wondering if there was a macro that would automaticlly match the headings of the new sheet with the other table, and fill in the ncessary dat, as on the other sheet.
View 3 Replies View RelatedHow do I make the column headings appear when I'm working on a large spread sheet so I don't have to scroll to the top each time to see where I am. This is on Excel 2007.
View 5 Replies View RelatedI have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range
Columns AJ through AX
Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE
Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
2013
Current Year
Q1-13
Q2-13
Q3-13
Q4-13
Totals
Awarded
£19,000.00
£4,000.00
£3,250.00
£0.00
£26,250.00
[code].....
I need to count the number of unique companies that receive money within a specific QTR. I have made this simple example, I have a Table called Awards, with Headings for DATE, QTR, Company, Awarded, on one worksheet, that I need to feed the data into a summary on another worksheet. What formula using table heading can I use to achieve the answer 3 unique companies for Q1-13.
i want to hide and unhide headings from all sheets at once (Excel 2013)
View 8 Replies View RelatedI have two data points a Talk Time Value lets say 2:08 and the number of calls lets say 10 and need to pull the column heading value for the number of agents needed lets say 2. My "Grid" is saved in one sheet and the my variable data values are in another. I need to somehow pull the closet match of my talk time value 2:08 and the calls value 10 to match up on my grid and give me the Column Heading which is the number of agents. Which in this example would be 2 agents. Just for reference in my other sheet I have my Talk time Value in C3 and my Call number value in D3. I tried various Index and Match formulas but have not got the one that I need to work the closet I have is =INDEX(Sheet2'B1:D1,MATCH(D3,INDEX(Sheet2'B2:D7,MATCH(C3,'Sheet2'A2:A7,1),0))).
example: in Sheet 2
ColA Col B Col C Col D Sheet 1 C3= 2:08 D3=10
Row 1 1 2 3
Row 2 1:00 0 3 4
Row 3 1:15 1 5 6
Row 4 1:30 2 7 8
Row 5 1:45 3 9 10
Row 6 2:00 4 10 11
Row 7 2:15 5 12 13
I have a workbook with 2 worksheets, Sheet1 and Sheet2 have the same column headings in Row 1 but they are not in the same positions.
What I need to do is read the column header in Sheet2 Column A (Cell A1) and copy the data from A2 to last row and paste it in Sheet1 in the first empty cell under the same column heading, which may be the Column D position instead of Column A like in Sheet2.
It needs to loop through all the columns in Sheet2, copying the relevant data to under the correct header in Sheet1.
I have one worksheet which comes out of an online application (for the purposes of this example I will call it "Online Sheet"). The column headings in this sheets are always named the same but could theoretically appear in any column address. For example, the column header "Completed" could appear in column "X", or "AT", or "ZZ".
On a second sheet (called "Code Sheet") I have to unscramble all of this data into a standardize layout. Each row value has a unique ID which appears on both the "Code Sheet" and the "Online Sheet". However, in the "online sheet" the unique ID could also appear in any column. Like the "Completed" column this column also has a unique column heading ("Unique ID").
Using Match I can calculate the column number in which the "Completed" column appears in this instance of the online data. For example, "Completed" = Column "25". However, I now need to use some lookup function on this column based on the row in which my "Unique ID" appears of the "Online Sheet" (which could of course be in any column in the "Online Sheet").
For example, if "Completed" is in column "25" and the "Unique ID" which I am referencing on the "Code Sheet" appears on row 14 in the "Online Sheet", then the lookup formula must return the value of column 25 row 14 on my "Code Sheet".
I need to work with the run times for some equipment. This is just hours and minutes and I get the raw data as like "Pump # 1 - 35:30". This is not date/time data per se, just a raw number of hours. I thought I would format the cells as hh:mm and for 15:00 I would just enter 15 to save time. No go, that becomes 1/15/00 00:00 and displays as 00:00. I discovered that if I just leave the formatting as general and enter 15:00 it works fine and displays right but the formula bar says 3:00 PM (Excel figured to format it hh:mm). When I enter 30:00 it again seems fine but the formula bar says Excel stores it as 1/1/00 6:00:00 AM (Excel figured to format it [h]:mm:ss but I deleted the seconds).
I can just set the formatting as [h]:mm to be consistent and enter the full 15:00 but I guess that after working that out I am just curious - can Excel store hours:minutes as just hours:minutes without turning it into a meaningless date? And without converting it to a decimal number? I don't want to total 1:30 + 1:45 = 3.25; I want the total to be 3:15. Again, it appears that [h]:mm will do what I need but it just seems unelegant to me that it is stored as some weird date and I wonder if it will cause a problem at any point.
See the attached excel workbook:
SAMPLE_PivotTable.xlsm
I have class monday, tuesday, wednesday & friday but no class thursday. How can I make automatically fill in dates for weekdays only and skip thursday using excel 2013's "Fill" function then "series" ??
View 6 Replies View RelatedWhat is the best way to filter a list of data with dual headers, using both those headers as the filter criteria? I’d like to extract 1 column of data to copy to another location using something like Advanced Filter to select 2 criteria ( Upper header and Lower header), and copy the one column with the matching header values.
The worksheet has the following peculiarities:
- 2-row headers
- Top row headers (main categories) are merged, spanning several columns (I can un- merge if necessary)
- Bottom row headers are sub-categories and have repeating names
Although the top headers are merged and the bottom headers have repeating names, each of the 2 headers combined creates unique labels for each column.
I have a table of data with column headings. I'd like to be able to filter the table based on the column headings, however I have a 'spare row' between the column headings and the data which I use to add new entries to the table using a macro (the macro copies the row and pastes it in one of the empty rows at the bottom of the table, then sorts the data which places it with the rest of the data).
The problem is, Excel won't let me apply a filter to the table if the column headings are separated from the data that I would like filtered.
I have a table with unsorted Time Data and Headings for each Row and Column (See attachment). I need to display this data in another worksheet as a scrollable list.
Then when I click on any list item, it displays only that data in the following format:
A1 = Time. A2 = Row Heading. A3 = Column Heading.
Easier to understand if you view the simple spreadsheet attached.
Please see the attached example. I'm trying to highlight cells only if 2 values in the row match 2 values in another row. If a book title/author combination matches that of another title/author combination in a different row, it would highlight. Some alternate rows will contain different titles by the same author, and some rows will have like titles by different authors...but they should not highlight.
The only situation where highlighting would occur is if the title/author pairing appears in another row.
I'm trying to decide if this would be a conditional formatting/highlighting rule, or if it would be a macro/vbs?
I'm using Excel 2013.
Pairing example.PNG
I have a friend using Excel for Macs 2011 and the column and row headings (ABC & 123) are not showing on the spreadsheets on the screen. How to turn them on. I fgure they got turned off somehow?????
View 5 Replies View RelatedI just discovered forms in excel and loving what custom functionality you can create with it; however, I need to include a table into the form but I've come to a road block. I don't see a way possible to render data from a query into a table inside of a form.
PS - I'm using Excel 2013
I am using excel 2013. I have the table look like the above, in the diagram 1, i want to have the macro such that it can be formatted to the 2nd diagram. Can this be done using macro ? [URL]
View 2 Replies View RelatedI have a very large excel data file, which I want to analyse using pivot tables. The problem is that while most of the columns are headed with the variable name (e.g. country) and have the list of variables displayed under that heading for each observation (e.g. Italy), the years are spread across the columns - i.e. the heading for column X is not "Year", but is 2003, with the next column being 2004, etc.
Is there a quick way I can re-arrange the data so that the layout is consistent and so that I can use it for pivot tables? I have way too many observations to do this by hand.
I threw together some sample data, just to test things out. It's very basic, one table of "Customers" and another table with the 50 US State's and their corresponding abbreviations. In the "Customers" table, there is a column of state abbreviations, and in the "States" table there is a column of state abbreviations as well. I have a relationship set up between these two in PowerPivot.
Each "Customer" in the "Customers" table has a unique "User ID". In some states, there are multiple "Customers" (User ID's).
When I try to pull a PowerPivot Table off of these tables, it's showing me all the states as being associated with every user ID. It looks correct if I just pull in the "States" column and the "User ID" column from the "Customers" table...
image1.jpg
But as soon as I drag in the "Full State Name" column from the "States" table, it screws up the PowerPivot Table and shows all the state names being related to the state abbreviations, and all the User ID's as being related to all the states. (This isn't the full image of the table, only part of it, since the full image would be too large).
image2.jpg
I'm used to doing everything with VLOOKUP's, and seldom used PivotTables at all in the past. But it was my understanding that these new PowerPivots would eliminate much of the need for VLOOKUP's.
I have a set of data that I formatted as a table, including headers. It seems that the columns are numbered, and after the column header is a number. i.e., Sales 2, GP Freight 3, etc.
I have spent some time researching this and came up empty. Is there a way to disable this part of table formatting?
I have some nominal data that I'd like to get into a pivot table (Excel 2013). For simplicity let's say it's a one-question survey with 6 respondents:
Q1: Dogs are better than Cats
Strongly Agree
Agree
Neither Agree or Disagree
Disagree
Strongly Disagree
Responses:
Agree
Strongly Agree
Strongly Agree
Strongly Agree
Agree
Strongly Agree
I can create a pivot table with this data and get the following:
Row Labels
Count of Q1
Strongly Agree
4
Agree
2
Grand Total
6
This all works nicely, however I require that the other options ("Neither Agree or Disagree", "Disagree", and "Strongly Disagree") be present, even if their values are 0, like the following:
Row Labels
Count of Q1
Strongly Agree
4
Agree
2
Neither Agree or Disagree
0
Disagree
0
Strongly Disagree
0
Grand Total
6
What I tried doing was adding a new column and calling it something like Ratings with the following:
Ratings
Strongly Agree
Agree
Neither Agree or Disagree
Disagree
Strongly Disagree
Then I set the Ratings column in the "Rows" section of the pivot table and the Count of Q1 column in the Values section. This is what happened:
Row Labels
Count of Q1
Strongly Agree
1
Agree
1
Neither Agree or Disagree
1
Disagree
1
Strongly Disagree
1
Grand Total
6
In the attached example, you will find column C which has a bunch of qualitative results. Also, in the file or worksheets, you will find Table A which has a color code range.
I would like to have a conditional format down column C that will reference Table A, regardless if it's on in the same worksheet or on another worksheet within the same file?
Using Excel 2013
I have a pivot table like the one below.
What I would like to do is filter the drill down keeping the total of the products (in bold) and showing just one of the name (just ENTA for Example).
Basically I would like to add a filter that Hide some of the data keeping the row total.
I'm Using Excel 2013.
Products
Sell out 4 weeks
Stock Units
Avg 4 weeks
Wks of stock
3160-24PC-AP12
1
[code].....
Using Excel 2013,
I clicked on a field in my RowLabels
I then clicked on the Filter Arrow for the RowField
The SelectedField prompted with the correct field of 4 possible fields
However the item I am looking for is not in the list but I can plainly see it on the screen.
Using Excel 2013.
I am trying to get DistinctCount from a field if another field in the same table is greater than zero.
PowerPivot DAX -COUNTIF
[Code] .....
The count returned includes all not just values greater than zero. How can I get just values greater than zero?
I have several competitors balance sheets (around 15), they all have the same structure, what i will like to do is summarize all this data into a pivot table to have a grand total but also be able to filter the data by single competitor.
I have tried to do multiple consolidation ranges, power pivot, pivot but i was unsuccessful, maybe i arranged the data wrong or im not using the right solution. im using excel 2013
I have a code that inserts a new column after every 7th column. I want to include a formula where every 7th column value is subtracted from the values present in the column before the 1st, or you can think of it as subtracting 7th column of the present group from the 7th column of the previous group. Example: The range of my data starts from col F, then
F (7th) New Column (G) H (1st) I (2nd) J (3rd) K (4th) L (5th) M (6th) N (7th) New Column (O) P (1st) Q (2nd) R (3rd)
So, New Column (O) = N - F
and the next New Column (W) = V-N ...
NOTE: Column G can be ignored.
I want to add a looping function to this so that it will continue to subtract for the other respective columns as well. How do I incorporate this into the following code?
[Code].....
I'm using Excel 2013.
My and a work college needed to combine our separate excel worksheets into a single document.
Office 2013 didn't have a function to "import sheet from file" so we used open office to import my .xlsx worksheets.
After we finished importing we exported the final workbook as .xls (so I could open it).
After opening the workbook on my pc (excel 2013) i notice some of the sheets no longer have column headers, but the row headings still exists. (No ABCD, only 1234)
Also I am unable to use features such as "Freeze Pane"
I suspect this was caused by importing and exporting through open office?