Sheet About 160 Rows Which Contains Subtotals And Filters
Jan 16, 2009
i have an excel sheet about 160 rows which contains subtotals and filters and the problem is taht when i insert the row in it it's speed will become very slow and i am using only one sheet in this file which contains data it takes about 30 to 40 seconds to get back on its normal speed and also sometimes when i insert data in it the speed problem is happen.
i use this file on two computers and the speed of both the computers is very good but this file showing same speed problem.
View 9 Replies
ADVERTISEMENT
Dec 12, 2008
Subtotal doesn't add cells hidden under a filter column but it does when grouping. How can I get groups to change a subtotal based on whether they are hidden or not. What I'm really trying to do is use conditional formatting to change the format when a group is expanded vs collapsed.
View 3 Replies
View Related
Oct 13, 2009
attached Pivot Table. There is a section, which I've marked, that shows how I want my Pivot Table report to look. I've had Excel 2007 kick me out a number of times when I've tried to filter and shift rows and columns to accomplish this and areas are frequently grayed out. Can someone please assist?
I'm attaching a file rather than going into a lot of detail here because I think a visual is easier here. The raw data, my attempt at the Pivot Table and my desired outcome all show on the same tab.
View 5 Replies
View Related
Jun 7, 2006
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View 5 Replies
View Related
Jul 24, 2014
I have a seating plan for my students that has all sorts of data in it and I want to hide certain rows from students. However, they are not all in a group, they are spread out. So for example I have row 3 with student name, row 4 with their target grade, row 5 with their current grade, then row 6 is another student with data on 7 and 8. After that I might have a gap and then the next student on row 10 with data on 11 and 12. I want to toggle on and off the data and leave the student seats. I'm thinking that Subtotals would be the best way, but not sure how to implement it. Do I put row headings on the side? Also I haven't used subtotals since Excel 2003.
View 1 Replies
View Related
Aug 8, 2014
I am trying to develop a compliance report with Excel 2007) based upon a simple pass/fail criteria. The subtotals must be tracked both by Device (column) and by Requirement (row). There are macros (not included in the attached sample) that hides both columns and rows. I successfully found an example which I modified to correctly calculates data for a column when rows are hidden (see GOOD function below). However, I'm totally clueless on how to calculate data by row when columns are hidden (see BAD function below). What I'm trying to figure out is highlighted in red in the attached spreadsheet.
GOOD
=SUMPRODUCT(SUBTOTAL(103,OFFSET(B$2:B$5,ROW(B$2:B$5)-MIN(ROW(B$2:B$5)),,1))*(B$2:B$5="Pass"))
BAD (returns 0 and includes a circular reference)
=SUMPRODUCT(SUBTOTAL(103,OFFSET($B2:$F2,,COLUMN($B2:$F2)-MIN(COLUMN($B2:$F2)),1))*($B2:$F2="Pass"))
View 14 Replies
View Related
Aug 6, 2008
I'm getting a runtime 1004 error "Cannot Shift Objects Off Sheet" right at the line when i am trying to collapse a subtotal (showlevels, rowlevel 2). I searched about this and i tested. I cannot find any comments, hidden comments, rows or columns. I cannot find any shapes (ran "Kill_Shapes" posted by Aaron Blood).
Sheets("Oxnard Planning 10 (all)").Activate
'SORT: Del Code (D), then Style (A)
Range("A1").Sort Key1:=Range("D1"), Order1:=xlAscending, _
Key2:=Range("A1"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
'Subtotal by STYLE
Range("A1").subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, 9, 10, 11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'Subtotal lines = Bold & Pink
ActiveSheet.Outline.ShowLevels RowLevels:=2 '<< ERROR:Cannot shift Objects off Sheet
With Range(Range("K65536").End(xlUp), "A1").SpecialCells(xlCellTypeVisible)
.Interior.ColorIndex = 38
.Font.Bold = True
End With
ActiveSheet.Outline.ShowLevels RowLevels:=3
View 2 Replies
View Related
Dec 15, 2009
In VBA, is there code to clear all filters from an inactive worksheet or does the worksheet need to be activated?
View 14 Replies
View Related
Mar 25, 2009
I have a Workbook containing seven or eight Worksheets. The first Worksheet is a data entry and options selection sheet displaying a selection of Textboxes, Option buttons, Combo boxes etc, the second sheet contains reference data, following these are a number of Worksheets that are calculated and filtered as a result of those options and inputs. A 'print' button on the input Worksheet then runs a Procedure that applies the relevant filters, assigns print areas and prints out the 4 or 5 filtered worksheets. I now want to expand this Procedure to take a copy of just these filtered Worksheets and save them as another Workbook (values only) for subsequent free editting.
I have started by selecting the relevant cells on the first filtered Worksheet, copying, opening a new Workbook, renaming the first Worksheet to match the one I'm copying, PasteSpecial formats, PasteSpecial the values, go back to the original Workbook, select the relevant cells on the next Worksheet, copying, opening the new Workbook, renaming the Worksheet etc etc. Now this seems to be a very labour intensive approach and I'm wondering if there is an easier way and how to do it. Ideally something like... taking a copy of the whole Workbook somehow changing all the cells to values only, rather than formulars then deleting the first two Worksheets (input and data ones).
View 4 Replies
View Related
Apr 7, 2014
I have an excel spreadsheet(Sheet1) with information in 35,000 odd trade lines, which is eventually compared against another set of records from another tab (Sheet2) in the same spreadsheet.
Have recorded a macro which does the lookup using the common parameters and returns the unique identifer in Sheet1 from Sheet2. Post updating the UID's in Sheet1 - range O:O, I need to select all the rows which are not matched and move it to another tab.
I apply filter on column O1 and select #N/A and select the complete range. Via VB I use the below code to move between to the visible row from filtered row:
Range("O1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
If the visible row is within the first 1,000 rows, it doesn't take much time. However if the same is around 25,000th row, it takes more than 5-6 mins in order to move to the visible row.
View 1 Replies
View Related
Jun 26, 2013
I'm Using Excel 2007 and would like to have some VBA to work with the following!
I have a simple pivot table (PivotTable1) in Sheet1 with three items in the Report Filter which has been named "ROUTE"
I have created a ComboBox in Sheet2 and have added the identical three items in via format control, cell link A1.
I would like to be able to use the combobox in sheet2 to operate the PivotTable Report Filter in Sheet1 as I would like to build a report whereas a user. Can only select the comboBox and does not see the pivotTable
Sounds simple but cannot get this to work no matter what I try.
View 5 Replies
View Related
Aug 5, 2013
I have a table which says that this is the amount of coloured cells we have in another sheet.
For e.g.
Field Name Code A
Item Description 5
Now, the item description column has 5 cells in another sheet which are filled in with "Yellow" Colour. So what i want to do is to click on this 5 in sheet 2 in this case as per the attached sample which takes me to the filtered result on sheet1 of 5 yellow coloured cells under the column of Item description including an additional filter of Code "A"
View 1 Replies
View Related
Jul 1, 2014
I have 3 pivot tables and with 3 filters each (they are all the same filters). I just want to change 1 of the filters for each of the pivot table (meaning the other 2 stay the same for all of the pivots). Is it possible to have a filter change automatically to match a filter in another pivot?
View 9 Replies
View Related
Jan 5, 2009
I am having a problem with a formula I think I should be able to get correct but not sure if I am able to do so. On the attached file I have a filter on the TIER's for "Is grater than or equal to 5". What I need is a SUMIF formula that will take into account the filters. This formula needs to separate out between "GER", "IRE" and "UK" in cells C37, C38 and C39.
I have a subtotal in cell C35 which gives me the subtotal of all countries but I'd like to be able to have the subtotal separated out between the 3 countries and also still have the ability to manipulate the data so I could select different TIER's or a range of TIER's and Cells C37 - 39 automatically update themselves.
View 3 Replies
View Related
Sep 15, 2009
Have a look at the attache example. I have inventory items which have a fixed value. I have several quantity columns to reflect various inventory positions. (OnOrder, WIP, ATP, ATS, OnHand, PAB as at date()...)
At the top of the sheet I need to show sum of quantities and sum of values. In order to compute the values currently I need to hide columns to the extreme right to do the math on a row by row level and then sum the rows and copy the the value to another cell. In this case the cells with yellow background.
Is there a way to be able to write a formula that would return the sum of the qty multiplied by the value without adding an additional column. I would need to function like with filters as well (like =subtotal())
View 3 Replies
View Related
Aug 28, 2012
I am using subtotals to create groups. Sometimes there is only one row but more often there are two or more rows. I am trying to run a macro that needs to select either one row (copy into memory) of which there will always be a blank row below that, given the space where the subtotal does its calculations. I have tried a couple things i.e.
ActiveCell.Select
Selection.End (x1Down) . Select
Selection.End (x1Down) . Select
or
Range(Selection, Selection.End(x1Down).Select
ActiveCell.Rows ("1:8").EntireRow.Select
Is there a way to have the macro start and if it sees only one row followed by a space (blank row of subtotal that creates the needed break, that it will copy that one row into memory or if there are two or more rows that it will copy all those rows up to the next break and put that into memory? There can be anywhere from 1 row to several hundred rows.
View 1 Replies
View Related
Jun 13, 2006
have an excel spreadsheet linked to a network printer, it contains a list of what each user printed, how many pages, and the total cost. It is constantly updated. The total cost column contains the wrong price, so Im using this formula:
=If(C2=0,"",SUM(D2/C2)*(H2))
in EACH field on the total user cost column to extract the CORRECT price. So, I started my macro, highlighted the entire TOTAL COST column, then inserted my formula into each field in that column. The correct price displays for rows that contain data, and rows without data are blank. However, when I try to create a SUBTOTAL (Data --> Subtotals) for each user, I get the following error: "To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet."
This is because I applied the formula to the ENTIRE column - even blank cells still contain the formula. How do I get fields without data to be completely blank?
View 6 Replies
View Related
Jul 12, 2006
I need the data "pulled down" into the subtotal row, so to get this after I subtotal, I'm sorting by C, and I've got some VBA deleting all rows where COLs A & B are blank (this is the longest part & the part I want changed the most - this gets rid of the non-subtotaled rows), extended replacing "Total" with "" in COL C and then inserting a lookup in A & B to get the data back next to the subtotals.
This takes really long and I'm sure there's a faster way to do this that I haven't thought of. All in all, I'm looking for something that will ONLY keep the subtotal rows, and will fill down the data to them while removing any non-subtotal rows.
View 9 Replies
View Related
May 27, 2007
Client Location Product Cost Sub- total
ABD Here Slurry $125.
ABE There Mud $525. $650.
Where I want to enter the cost and have Excel do the sub-total automatically.
Is there an easy way to do this, remember I am new to all of this? The spread sheet is already 147 entries long and will only grow and I don't want to have to figure out the sub-totals each time.
View 9 Replies
View Related
Aug 27, 2007
After using the subtotal function, I need to highlight and bold the subtotal rows. There are thousand over rows and it is impossible to do it manually, does anyone has a solution to this?
View 9 Replies
View Related
Dec 31, 2013
Formula/code to change the position of where the subtotals are placed. I don't want them appearing at the beginning or end of the data set but in a separate column beside each data set. how to access the code so I can try and alter it myself.
View 14 Replies
View Related
Apr 24, 2014
I have sorted my data by three layers. First by Budget Center, then Invoice, and then Account. I am having trouble writing a formula that will total the amounts by account with respect to its invoice and budget center.
excel forum2.xlsx
View 4 Replies
View Related
Dec 31, 2009
I have a column of numbers that after each sum there will be a subtotal. If the sum is a negative number then the new subtotal will be 0. Attached is a sample.
View 3 Replies
View Related
Dec 11, 2005
Let's say I have this set of data
A..................B..........C
date_______amt_ sub
12/1/05____ 1000
12/1/05____ 2000
12/1/05____ 5000 7000
12/3/05____ 2000
12/3/05____ 9000 11000
12/6/05____ 1000 1000
12/7/05____ 4000
12/7/05____ 2000 6000
So we see a subtotal according to the date, where the total values in chronological order are calculated to be
12/1/05 7000
12/3/05 11000
12/6/05 1000
12/7/05 6000
What sort of formula, then, do I put in column C that subtotals values in B according to the date in A?
View 10 Replies
View Related
Oct 21, 2007
it state "Use the subtotals command to sum the totals for each sales person) *Hint: convert the list to a normal range before calculating the subtotals.
I highlight Sales person and click Date but subtotals key is not showing up. i have attach the file its in the Subtotals worksheet.
View 14 Replies
View Related
Jun 4, 2009
I am struggling to get the totals from a Pivot table using Getpivot data.
could someone please advise how can i get this.
=GETPIVOTDATA("Sum of USD Value",$A$3,"Security Code","Total")
View 6 Replies
View Related
Nov 21, 2011
I am trying to work out a spreadsheet that I am using conditional formatting to highlight every two rows. I have 2 lines for each item and i need them to be grouped together. I can get the formula =mod(row(),4)
View 4 Replies
View Related
Jan 3, 2012
I need to count the number of times a word appears in a column in a pivot table and add the total value in the next cell minus 1 if the number is more than 1.
MANAGER
(All)
QA
(All)
Count of 1ST LVL ERROR
Error Type
Count
[Code]...
The totals in C come from a pivot table created from the data in a separate sheet. I need to dynamically show who made an error and the frequency of that kind of error. I've gotten the Error Type count to display according to the word in column B, but column C will show how many times that a specific notation was made.
For example:
"Annualization" appears in column B 3 times, but column C shows that one of the descriptions is listed twice in the separate sheet. The current formula shows 3, but it needs to count the 3 in B, then add any number that is more than 1 minus 1 in the relative C cell to yield 4.
This is my count array: =SUM(LEN(B6:B4506)-LEN(SUBSTITUTE(B6:B4506,D6,"")))/LEN(D6)
I've also used: =COUNTIF(_1ST_LVL_ERROR,"*"&D6&"*")
I don't know how to get the second column to factor in, though.
View 1 Replies
View Related
Apr 26, 2012
I am trying to add a subtotal to the RIGHT of my table for every change in DATE. The desired result looks like this:
Number
Vendor
Run Date
Amount
Subtotals
12580A
4/12/2012 233,220.14
[Code]...
Right now I am manually typing in a sum formula at each change of the value. Is there some way to automatically perform this calculation and have it be in a column of the table?
View 7 Replies
View Related
Oct 19, 2012
I have a report that needs subtotals between different teams. The report starts at cell A10.
In column A is the team name and then columns D through to G contain monetary values.
So the Admin team might contain data from A10-G15 (although this is variable depending on activity).
There is then three rows of blank cells before Sales team data appears - this might contain data for a dozen rows.
Then there is three rows of blank cells before Training team data appears......and so on.
There is ALWAYS three rows of blank data between teams and the goal is to enter totals for each team in the second blank row.
So in the above, Admin team totals would appear in row 17 in columns D-G.
Sales team data would kick in at row 19, run for a dozen rows (down to say row 30) and at row 32 the toals would appear.
Training team data kicks in at row 34 and so on.
View 1 Replies
View Related
