Formula To Expand Headings To Table Columns
Jan 25, 2014
The attached workbook has a fragment of a table . The column heads are letters representing job nos. The first row is of numbers which will always be multiples of 1000. Subsequent rows are the factors of each number by each number.
My problem is how to structure the columns of the second table so that if a number has a factor of 1 there is one copy of that column head. if the number has a factor of 2 then there are 2 copies of the head, and so on.
The table can vary in size but the first row of each column will always be an integral number of 1000's (e.g. never 1250).
I have not been able to attach the workbook as "ADDFILES" is not visible in the download screen, I hope the above description will be enough.
View 3 Replies
ADVERTISEMENT
Aug 29, 2007
I 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 Related
Oct 14, 2013
As far as I can tell there needs to be a nested loop with some kind of variable inside a variable, but to be honest it's well beyond my current skill level... I have the file hosted on my Google Docs below.
[URL]
Company
Item
Quantity
Costs
Tesco
Cheese
4
50
Tesco
Milk
5
50
[code]....
View 3 Replies
View Related
Feb 10, 2014
Currently, Columns EFG are grouped.
I have a userform with checkboxes. With the click of check box, i would like for it to do the following.
If checked, it will expand the group. If unchecked, it needs to stay collapsed.
Here is an example of code i currently have and is not working.
[Code] ......
View 2 Replies
View Related
Oct 12, 2002
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 Related
Apr 30, 2003
Is there a way to password protect a document AND still allow for a user to expand or collapse columns?
Currently, it appear that when a worksheet is password protected, one cannot expand or collapse a column.
View 9 Replies
View Related
Mar 30, 2013
I have 2 drop downs that when changed, auto refresh all pivot tables. My problem is getting the columns to auto-adjust based on the refresh or change of the data in the pivot table. Listed below the code I have thus far for the auto-refresh on pivot tables. How to auto expand all columns simultaneously.
The pivot tables are based off of tables on a different sheet(TOS Tables). So the code listed below is in the table sheet, not the pivot table sheet (TOS Customer Level). So followup question will be, which sheet to put the auto-expand columns code?
VB:
Private Sub Worksheet_Calculate()
'If data on this worksheet changes, refresh the pivot table
Sheets("TOS Customer Level").PivotTables("PivotTable2").RefreshTable
Sheets("TOS Customer Level").PivotTables("PivotTable5").RefreshTable
End Sub
View 3 Replies
View Related
Apr 2, 2014
See the attached excel workbook:
SAMPLE_PivotTable.xlsm
View 2 Replies
View Related
Sep 28, 2007
I have a simple table Attached. (A simplified version of a much larger table) The data continues on for thousands of entries and there are quite a few more columns. Names are dynamic in that names will be added and deleted over time. (There is a list of names on a different worksheet that updates as users are added.)
I wish to Consolidate the Data Under Different Titles. The data in the NAME column will Consolidate to list each name in Alphabetical order. The data under the RAP COUNTER column should count the number of YES entries per NAME under the new title ATTEMPTS. The data under the RESULT column should Count the number of KILL entries under the new title KILLS. And the Data in the TG SCORE column should average all entries per NAME under the new title TOTAL SCORE. I tried to do this with a pivot table to no avail. I could write some programming to accomplish this, but I am am amateur, and I also worry about time required to run a macro for thousands of lines.
View 5 Replies
View Related
Feb 26, 2008
I have a question regarding Excel 2003.
I have to complete a task using Excel. Normally I would complete the task using a database, but this one needs to be complete using Excel (for others to use afterwards)
I have a flat semicolon delimited text file which holds x amount of lines OR I can get the data using an ODBC connection (directly from the DB)
Each line starts with a date, a name, a second name, and a price.
See: 1.input.jpg
200080221;AAA123;excel;0.34765
200080221;BBB123;access;0.16278
200080221;CCC123;powerpoint;0.35894
What I would like is the date on the horizontal row and name and second name as headline, and the price as the data.
See: 3.result.jpg
How can I solve this using Excel?
View 5 Replies
View Related
May 6, 2014
I have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)
How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.
View 1 Replies
View Related
Dec 27, 2011
I'm using Excel 2010. One spreadsheet I'm using has 1048576 lines and is a XLSX file. The other file has 65536 lines and is also a XLSX file. When I try to do a VLOOKUP, I'm given an error stating "invalid reference. This file version cannot contain formulas that reference cells beyond 256 columns or 65536 lines.
If both are XLSX files how do I expand the amount of columns in the one spreadsheet with 65536 lines? I thought all XLSX files had a million lines.
View 4 Replies
View Related
Nov 19, 2006
What 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.
View 3 Replies
View Related
Feb 26, 2014
Still finding the SUMPRODUCT function a bit challenging to fully grasp.
This formula works great to sum the miles (Q16:Q38) the team (A16:A38) drove in a particular month (B16:B38)
=SUMPRODUCT(--ISNUMBER(MATCH(A16:A38,INDIRECT(E3),0)),--(MONTH(B16:B38)=E7),Q16:Q38)
How would I expand it to check yet another column say (S16:S38) for the term " Charge Call" ?
So I can see the miles the team drove in March for all the charge calls.
Where can I go to study and learn more about SUMPRODUCT. Seems like just what I need to build interactive type dashboard reports.
View 2 Replies
View Related
Mar 31, 2009
I have a cell set up with a formula to copy the content from another cell. However, the cell won't expand to fit the text. If you delete the formula and just type in the cell, it does expand. See attachment.
View 5 Replies
View Related
Dec 8, 2006
Need to expand the crteria on this array?
{=SUM(IF(LYIND1=1,IF(PROFILE!$G$405:$G$435="EOM",(PROFILE!$AU$405:$AU$435),SUM(IF(LYIND1=2,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436)))))))-SUM($K$16:N16)}
I need to add
{=SUM(IF(LYIND2=1,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436),SUM(IF(LYIND2=2,IF(PROFILE!$G$407:$G$437="EOM",(PROFILE!$AU$407:$AU$437)))))))-SUM($K$16:N16)}
View 3 Replies
View Related
May 2, 2014
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.
View 8 Replies
View Related
Sep 29, 2006
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.
View 4 Replies
View Related
Jan 19, 2009
I am trying to figure this out. I have a userform with one listbox with 7 columns that displays a range. It is working except i cannot get the column headings to work.
View 4 Replies
View Related
May 18, 2014
How do I get the effect of hiding unused rows/column in the sheet, including headings, while the parts in use still display headings?
The pick below explains what I mean. [IMG][/IMG]
View 2 Replies
View Related
May 17, 2007
I believe it should be quite a simple thing...and probably has something to do with the OFFSET function...but I cannot seem to put my fingers to it.
All details are mentioned in the attached ZIP file...so I won't repeat myself here.
View 10 Replies
View Related
Aug 30, 2013
I have hundreds of rows of data which I need to sort into headings in a new tab.
E.g Column D has about 40 names which occur many times. Column G has 4 digit numbers which are unique.
I want to create in a new tab a column for each of these 40 names, using that name as the heading and then list the 4 digit numbers unders that heading. So if the name name "John" appears 20 times in Column D, it will get a heading in the new sheet and there will be 20 unique numbers listed below it from the data in Column G.
View 3 Replies
View Related
Apr 11, 2014
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 Replies
View Related
Feb 8, 2010
Is there a way to hide the formula bar and headings for a specific worksheet(s) with a workbook but not all the worksheets? In other words, I want to hide the formula bar and headings only for certain worksheets. When I select hide on the show/hide ribbon, it hides it for all the worksheets within a workbook.
Also, is there a VBA code that can be written to hide formula bar and headings for a selected worksheet?
View 9 Replies
View Related
Jul 6, 2014
=IF(E14<=0,0,IF(N9="yes",MAX(E15*C15,30),30))
I am currently using the above formula and need to make an addition to it.
If D8 is greater than 9000 and less than 9999 then the entry will be 35 rather than 30. Any other entry in D8 would leave it at 30
View 5 Replies
View Related
Sep 11, 2009
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
Table 1
Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348
Table 2
City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B
View 2 Replies
View Related
Jun 27, 2007
I need to Match to columns of one table to another two columns of another table, both tables are on the same worksheet.
One table has 500 rows more that should not be, I need to find out which of these 500 rows should be deleted, so the two tables should be same. So the batch number and corresponding amount should tie to another tables batch number and amount.
View 9 Replies
View Related
Feb 20, 2013
I have a data validation list that has approx 40 items.When I select the cell with the drop down list - only 10 or so items appear . I then have to use the scroll bar on the side to look further in the list. Is there a way that more than 10 or so ( possibly all ) items appear in the drop down box when the cell is selected - or is there a way that one can scroll down using the mouse wheel. ( I have to use the scroll bar at the moment ).
View 2 Replies
View Related
Nov 15, 2009
I need help please. Using a macro, I have selected the range C7:C12. Now I need to keep this selection and expand it 18 columns to the right. This would give me a selection of C7:U12.
The original range changes at various times. Some times it might be C7: C452 etc.
I know it's easy, but I can get the code to keep the selection.
View 6 Replies
View Related
Jan 22, 2009
If I select lets say cell "A46", expand the selected range from A46 to Q46 and paste that range to another sheet. I know how to do the copy and paste features, but I can't figure out how to expand the selected range. I will always be selecting a cell in column "A" and going to out to "Q".
View 9 Replies
View Related
