How To Make Self Expanding Table
Jun 19, 2014
Is it possible to make a self expanding table. What I have is a sheet with a table with loads of biking tracks with track number, date, nr of miles and so on...
I'd like to make a table on the second sheet so that I can see only the tracks that auto fill as I put in info about number of miles or date or track number.
And I'd like it to auto expand so I don't have a lot of wasted space on the page. I'd like the TOTAL amount of miles, when i put in multiple tracks, to move up or down when I add or remove tracks...
View 6 Replies
ADVERTISEMENT
Jan 9, 2014
I have a problem in expanding/collapsing fields in pivot table.
I have source table with GROUP, SUBGROUP, ACCOUNT and AMOUNT. In pivot table I've put GROUP, SUBGROUP and ACCOUNT in Row labels respectivly, and AMOUNT in values. There are same names in SUBGROUP for different names in GROUP. (for example: groups are Production costs and Distribution costs and in each of them there are subgroups Personnel costs and Other costs).
The question is: Is it possible to expand only field Other costs in group Distribution costs while field Other costs in group Production costs stays collapsed?
View 2 Replies
View Related
Jul 9, 2012
So I have recorded a macro that filters for certain criteria in a master list of projects, then copies the projects that meets this criteria and pastes it into a timeline in another worksheet. However, because the list of projects gets bigger and smaller when the filter criteria changes, right now I have to create twenty extra rows so that the pasted list doesnt ourgrow my timeline. How I could input a few lines in my macro so that the table for the timeline expands as the number of prjects grows or shrinks.
One method I have tried is to copy the filtered data and "Insert Copied Cells" into the table, however I don't even get the option to insert copied cells after I have copied the data...
View 3 Replies
View Related
Feb 5, 2007
I would like to use some VB code to search a Pivot Table, find a specific value and show the details relating to the value. I used the following code I have obtained from another thread in the forum
Sub Expand_Pivot_Values()
Dim rngFind As Range
Dim rngPTData As Range, rngPTC1 As Range
Dim rngF As Range, rngP As Range
Dim strFind As String
Set rngFind = ActiveSheet.Range("J3:J6")
'set the pivot table data range - see help file for property description
Set rngPTData = ActiveSheet.PivotTables(1).DataBodyRange
'set start cell for find
Set rngPTC1 = rngPTData.Cells(1)
For Each rngF In rngFind.Cells
'set variable to find........................
This code works fine but only identifies the first time the value appears in the pivot table and shows detail relating to that value. I need add to the code to search the pivot table and find recurring values. For example if the value is "2" I would like to show detail on each "2" value there is in the pivot table.
View 7 Replies
View Related
Oct 29, 2012
I have two pivot tables where if one expands a subtotal (in this case 'manufacturer') to give the lines below (products made by the manufacturer), is it possible for this to be mirrored in the second pivot? I noted a previous comment on using slicers across two pivots but I cannot do this in this instance, although I am using Excel 2010.
View 1 Replies
View Related
May 3, 2014
I have a number of array formulas that refer back to the table in the top left. The array formulas are in Columns I and L, and cells N200, N203, and P203.
Normally when I add a new line to the top left table (usually by clicking on the empty box below the previous final line and typing the date), the table expands by one row and the array formulas all adjust to match, i.e. all the A180/C180s in the formulas become A181/C181s, etc.
However every once in a while (maybe once a month), the arrays simply refuse to update, and I can't figure out why. When they fail, they all fail, it's not hit or miss. You'll see in the attached sheet that though I've added a line to the table (A181) and data to that line, all the arrays still read through A180/C180.
Why this randomly fails to update, and if it's something I'm doing incorrectly?
And that leads to an associated question. In the past when this has failed in this manner, I've laboriously gone through and changed all the cell references manually, in every single individual cell. (After doing it once, the arrays usually magically start auto-expanding again.) I'm certain there is a way to make those formulas refer to a named range, instead of all being manual references, but after spending an hour on it, I keep getting #VALUE errors.
I've been using [URL] ..... and pages like it as a reference without success. Obviously, I would prefer that the formulas auto-update without issue per my first issue above, but in the event that they don't, it'd be really nice to just change the ranges in one location and have that propagate across the sheet.
View 13 Replies
View Related
Feb 13, 2013
I need to create a dynamic list from a table of data.
I have performance data for 110 different pumps. Data points are generated every hour, and the table is updated with new data periodically. I want to automate the population of a list of 6 different pumps, and specify the date range populated. To put it another way: I want to place data from Pumps 1 through 6 for all of November into Columns A through F. Then I want to clear that data, and show data for Pumps 105-110 for last week in those same Columns.
One of the main goals of doing this -- other than quickly narrowing a field of data -- is being able to quickly chart this data on a scatter plot with a custom format. The pumps are grouped together by region, and individual pumps have specific purposes. So I need to quickly generate graphs with a series' color scheme or formatting that is consistent and logical between different pumping regions.
I haven't been able to make pivot table work because of the graphing issue, and also because of the way it handles data points and presents data. I would like to make this work with excel functions and maybe some filtering, but I'm not opposed to figuring out a VB script if you think that's what I should use.
View 1 Replies
View Related
Mar 27, 2012
I 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.
View 1 Replies
View Related
Jun 17, 2002
How to (by vba or whatever):
1) convert the Matrix data into the data table, and;
2) convert the data table into the matrix data
Matrix data (example)
share Ashare Bshare C
springsellholdbuy
summerbuybuysell
autumnsellholdhold
winterholdsellsell
data Table (example)
springshare Asell
summershare Abuy
autumnshare Asell
wintershare Ahold
springshare Bhold
summershare Bbuy
autumnshare Bhold
wintershare Bsell
springshare Cbuy
summershare Csell
autumnshare Chold
wintershare Csell
View 3 Replies
View Related
Jan 22, 2007
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.
View 6 Replies
View Related
May 3, 2006
It is possible to make a table in the header in excel like word? If yes, how? I think my question can be answered her, but the links don't work anymore
http://ozgrid.com/forum/viewthread.php?tid=2952
http://ozgrid.com/forum/viewthread.php?tid=4391
View 2 Replies
View Related
Aug 19, 2012
i want to make the pivot table using vba macro. the sample workbook looks likes this [URL]...
View 1 Replies
View Related
Aug 14, 2014
Or at least with a formula?
This is the chart formula for the report:
=SERIE(Report!$B$10;Report!$A$11:$A$30;Report!$B$11:$B$30;1)
View 3 Replies
View Related
Mar 27, 2009
In the attached file (xlsx) under 'Database' Tab poeple have indicated their preferences (multiple choices) for different food items "specialties". The specialties are grouped under broader buckets called "groups". The specialties are bucketed into groups in a way that people end up more than once in each specialties and groups due to their muliple selections. When we create a pivot by specialties (Pivot Specialties tab), each person appears only once for each specialty...it's great. But, when we create a "group" pivot (each group has multiple specialties), now, people appear more than once for each group. Is there a way, each person can show up only once under each group so the group count does not appear to be misleading?
View 3 Replies
View Related
Feb 25, 2012
This is how my pivot table would look like to start. Over time I would refresh and more things will be added. I want to make a Pie chart, and everytime I refresh the Pivot I want the Pie to get updated.
[IMG][/IMG]
BUT I want to do this on a separate tab. So basically I want to do a Vlookup of sorts. Where all the Fruit items are pulled and automatically updated when I refresh the Pivot. Then I would do the same for Veggies.
View 4 Replies
View Related
Aug 14, 2008
Our system can create an SLK file showing customer open orders. It is ALWAYS in the exact same format. I created a macro to open this file, manipulate the data (convert text to numbers, convert text date to actual date, and a few other things) and then make a pivot table.
When I try to run it it gets hung up on the pivot table and the debug highlights the BOLD below:
Columns("F:L").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"SOLIST!C6:C12").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
The RowField, ColumnField, and Page Fields are correct.
The QTY is the data that goes in the field.
View 9 Replies
View Related
Aug 6, 2005
I've got many file (one file for one month) and number of records are nearly 65K for each month. So i must separate data for each month in one files. As the result, I;ve got so many files that i must generate report from. how can I generate privot table from many excel files with the same database structure.
View 8 Replies
View Related
Apr 19, 2006
I have made spreadsheet that calculates my total cost of making ice cream mix based on ingredient costs of two variables (Van_Gal, and Choc_Gal), for example 550 gallons and 750 gallons respectively. The worksheet calculates the total cost of making the Vanilla Gallons and the Chocolate Gallons. I’m not a VBA or Macro wiz, and now I’d like to Make a macro that will allow me to make a “table” of calculated costs associated with different assumed Vani_Gal and Choc_Gal amounts and then print the values; and then go down a row and print the cost of another amount of Vani_Gal and Choc_Gals until some preset end has been reached. For example:
Van_Gal; Choc_Gal;TOTOutput;avg cost
550; 750; 1300; $4,000.00; $3.08
600; 700; 1300; $4,250.00; $3.27
Let’s say that I want to know what the tot cost and avg cost per gallon for various combinations of Vanilla and Chocolate Mix from 550 V and 750 Choc for various combos and have it increase in iterations of 10 Gallons (i.e. 560 Van and 740 Choc); Maybe I'd use a " loop" that repeats calculations and prints them for different combinations of Van/choc until 800 Gals Van and 500 gal Choc. Once I have a table of values, I could sort it and find an optimal production level (with the Minimum avg cost). how to make a macro that can crank out a table like this?
View 4 Replies
View Related
Mar 16, 2009
I have an SQL Server that has a query (a view stored on the server) which takes roughly 10-20 seconds to execute. This execution time is the same in Excel 2007 if I import data to worksheet/pivottable. It is also the same in Excel 2003 while importing data into a worksheet. It returns about 350 rows.
However, if I try to make a pivot table in 2003 based on this query, it is much slower. I can set it up fine, but refreshing this pivot table takes at least 10-20x as long to complete, in the order of 5-20 minutes, to sometimes taking so long that I give up and kill it by hitting escape a bunch.
It's important to note that I am forced to used Microsoft Query to set up a pivot table in Excel 2003. There is no other way, I believe.
While it is running, Excel just sits there saying "Waiting for data to be returned from Microsoft Query".
Here is the weirdest part. Once the refresh starts, Microsoft Query loads in the background, you can see it on the taskbar. I believe that is expected behavior, but if I click on MS Query and view its datatable, I can see all the data is sitting there.
So what is the hold up? MS Query has the data. Excel is waiting for it to be returned. What is going on? And remember from above, if I do a simple import data to worksheet, MS Query is not used and everything is fast.
So my question is does anybody have any tips/advice on this issue? I need a way to not use MS Query for a pivottable's data source or I need a way to make MS Query not freeze up. I don't get why MS Query needs to be involved in the first place since I am not doing any parameters or so forth.
View 7 Replies
View Related
Aug 9, 2013
how can i extract all number values from a table column and list them on a separate worksheet? there are blanks in the table column, which have to be omitted.
View 14 Replies
View Related
Jan 11, 2013
I am trying to run a countif function on another worksheet to make a table of raw data.
Look on sheet SC-01 in Column H for "No" and return the count...pretty simple
Code:
=COUNTIF('SC-01'!$H:$H,"No")
What I need it to do is determin the sheet name based on the string in the column A
Control Countif Function
SC-01
4
SC-02
SC-04
SC-05
SC-06
I have tried various combos of Indirect and concatenate, but I keep getting a ref error.
View 2 Replies
View Related
Jan 10, 2010
I am tasked with putting together an Excel Spreadsheet to analyze our accounts. There are over 80 items for the accounts - about 20 Income and about 60 expenses. These are for various years - actually the last 5 years - by months.
So, I am trying to figure out how the best setup could be done to make this easier for pivot table analysis.
So I have 12 "rows" for each account item for each year.
Maybe I should wait for comments and/or questions before going further.
Just need to figure out how to set up the workbook/worksheet.
View 11 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
Apr 4, 2014
I'm having difficulty trying to get the second file appended to the bottom of the 1st imported file. I get "run-time error '13' type mismatch". There is no difference between the two files. I'm thinking there is a problem with my range statement in the second file import, but this range works fine in other macros. Here's my code so far:
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\server1usersmydataIMPORT1.CSV", Destination _
:=Range("$A$1"))
.Name = "IMPORT1"
.FieldNames = True
.RowNumbers = False
[Code] .........
View 7 Replies
View Related
Oct 23, 2009
[qoute]I have five cells that I am working with cells A,B,C,D & E. cells B & C are time formated. Cells D & E are number formated and equal the number of hours between cells B & C. the difference is that if cell D is less that 2 hrs then cell E is to equal 2 hrs but if cell D is over 2 hrs then cell E equals the value of cell D. All this works fine but I am needing to add in the value of cell A. So if I have say CT in cell A then cell E will equal the value of cell D no matter if is less than 2 hrs[/quote]
View 5 Replies
View Related
Aug 9, 2009
in this example below i have record entrys of tps,rpm,etc etc in time.
typically 4 records every 1 second.
how can i make the data 10 rows for every second?
i.e can we take the column readings after D i.e row2 then at row4 and then insert 8rows between the 2 and enter averages.
1353ecu *ABCDEF1LogIDLogEntryDateLogEntryTimeLogEntrySecondsTPSRPM21########13:47:190.55517.25491406.2532########13:47:190.80416.078431531.2543########13:47:191.03716.470591437.554########13:47:191.27223.529411343.75 Excel tables to the web >> Excel Jeanie HTML 4
the whole sheet is pretty big
1353ecu ABCDEFGHIJKLMNOPQ1LogIDLogEntryDateLogEntryTimeLogEntrySecondsTPSRPMBatteryCoolantTempAirTempTimingAdvO2SensorSpeedInjDutyCycleKnockSumLoad1BOctaneFlagknock_flag21########13:47:190.55517.25491406.2513.63938195.8122210.956483.2854850031.2100032########13:47:190.80416.078431531.2513.71271195.8122200.897923.2854850025.2100043########13:47:191.03716
View 43 Replies
View Related
Mar 29, 2014
copying a document that was made in word into Excel. I set the print area and page size but it seems I can keep adding columns. When I check the print preview it still shows the one page but it appears wider.Will it automatically create a new page if the page size limit is reached?
View 2 Replies
View Related
Jan 7, 2014
This is probably something very simple, but I need to find a way to stop row heights expanding when text in a cell wraps. Basically, I have a spreadsheet of projects, and one column is for 'notes'. When the text in this column starts to wrap, Excel automatically increases the row height to make all the text visible - but I don't want that to happen.
I guess there's a button to press somewhere, but I can't find it!!!
View 4 Replies
View Related
Jan 24, 2007
I have a bunch of nameds ranges that are essentially entire rows. For example, Rows 1 through 5 are named range "Range_A", "Range_B" is Row 10 through 20. I was looking for a way that I could have the value of one range assume the value and size of another range.
For example
Range_A = rows 1 through 5
Range_B = rows 10 through 20
Now, if I use the following macro all that happens is the first five rows of data is copied over to Range_B while Range_B's other 5 row values are not copied over.
Sub Example()
Range("Range_A").Value = Range("Range_B").Value
End Sub
What I want to have happen is have of Range_A expand to the same number of rows the other range has (which is 10 in this case) and assume the value of Range_B. Is there a way to do this? In my workbook I have a number of ranges that need to be replaced with other ranges and I want them to assume the size and values of the range they are supposed to equal.
View 6 Replies
View Related
Jun 9, 2007
I have a script that converts any List based Data Validation cell into a combo box when you click on it...What I am trying to do now is find a bit of code that will auto expand the combo box.
View 3 Replies
View Related
