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] ......
I have several groupings of rows in worksheet. I need to have each separate grouping be able to automatically expand or collapse based on a value in each group.
ie-
Grouping Rows 11-15
Grouping Rows 21-25
Grouping Rows 31-36
If cell values in A10, A20, or A30 = 1, then the grouping below it would need to be collapsed, and if not, then expanded.
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.
I suspect I need a class module for this...
How do I trap the event when a user chooses to expand / collapse outlines (columns in this instance) on a worksheet?
Basicallly, when attempting to expand I want to prompt the user to enter a password and then unprotect the sheet. And if collapsing to prompt again and then protect the sheet. I have this bit covered, just not sure how to trap the event.
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
Is there an easy way to collapse rows and columns on all sheets in a workbook.
View 3 Replies View RelatedHow can I check (using VBA) if 2 columns (F and G) are already grouped.
View 7 Replies View RelatedI have a spreadsheet where columns belonging to the same topic are grouped together. For every group I want to calculate the sum of numbers in a row. How can I get all the columns belonging to a group?
View 8 Replies View RelatedI have several years worth of monthly data that I would like to keep hidden so it is printer friendly but I also want to have a chart of all that data... Is this possible? Only idea I have is to basically recreate the data sheet but I don't really like that solution since it adds steps to the process and increases the file size...
View 2 Replies View RelatedI have a column containing ranges of apha-numeric codes that I wish to expand into an adjacent column as the following example:
AL 0-4
AL 0
E 4
AL 1
H 16-23
AL 2
[Code] .....
I would like the VB to distinguish and expand the different range permutations from the first column, leaving single codes intact and separating comma separated codes as shown. If it makes it easier, I manually split out the comma separated codes onto separate rows.
I have checked to see if this has been answered before and can not find an exact reply. My problem is that I have a combined column of address: Suburb, State, Postcode - which needs to be broken into three which not be done accurately using tet to column or fx - left mid or right. The issue is that the Suburb could be made up of one or two Words with a space between. So I can not separate using text to columns as in some cases the suburbs comprised of two words will put the second word in the "state" column. It can also not be done using Left, Right,Mid, as they number of characters differs in each line. example problem with columns to text.jpg
COMBINED ADDRESS SUBURBSTATE??POSTCODE??POSTCODE??
ALTONA NORTH VIC 3025ALTONANORTHVIC 3025
BONDI NSW 2026 BONDINSW 2026
WOOLLOONGABBA QLD 4102WOOLLOONGABBAQLD4102
TOOWONG QLD 4066 TOOWONGQLD 4066
NOVAR GARDENS SA 5040NOVARGARDENSSA 5040
I have data in excel sheet in the below format:
Existing view.png
How to write a VBA code or Macro to get it in below format:
Required View.png
Timestamp column is the unique key.
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.
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.
I created a PivotTable that works fine. A user can click on an option and the subcategories expand; then he can choose one of those and a new group opens, etc., going 4 or 5 levels deep in options.
What I need to do is: If he then wants to start over and choose another option in the initial level, I want all of the subcategories to reset to their original closed state (unexpanded). As it is now, when he goes back to the original choice, that is closed, but everything inside it is still open as he had selected them previously.
I am trying to build a worksheet which has an account number in column B with several more rows of content starting in column C and beyond. I would like to be able to hide the 9 rows under the row with the account number by double clicking on the account number. I found this code which will work by collapsing to the next account number, but it collapses the account number underneath it too. Is there a way to collapse only the blank cells in the column until the next number or to program the code to only collapse/hide the next 9 rows below the double clicked cell?
View 2 Replies View RelatedI'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
I have aprox 700 groups of cells "I selected a set of cells and named them". these groups cover 3500 rows. There sizes very in the number of rows per group but, all have the same number of columns.
What I'm tring to do is create a summary sheet. I want to copy all groups that have meet a single criteria to another sheet. The criteria will always be in the same column. The criteria is part of a function and will change each time I import new data into my excel sheet. Am I even close by trying to group them like this?
I have an Excel file with 70+ tabs and was wondering if there was some way to group them together, short of just making separate worksheets.
View 6 Replies View RelatedI'm working on a spreadsheet to track student test scores over the course of the school year. Each score set is grouped by student, but we need to be able to realphabetize when new students are added. is there some way to accomplish this?
View 3 Replies View RelatedI have a few pages of information and have grouped rows collapsed to show only the summary information. When I try to print only visible summary rows, whether I use print, print selection, copy and paste to another sheet or copy and "paste special" values to another sheet, all the hidden/grouped rows print or the summary rows print separately on different pages. Is it possible to print only the summary rows.
View 3 Replies View RelatedI have a spreadsheet with one column for each day. I've grouped the days into months and now I would like to minimize (minimize each month) Is it possible to close all, without having to select each group?
View 1 Replies View RelatedI have a bunch (between 5-10) textboxes grouped together into one object.
When I click one of them, I want its value to be written to a cell on the active worksheet. In other words, simply would like to read the value of one of the textboxes in the group. This sounds trivial, but at this point, I haven't been able to find the correct syntax to access this property. It seems that the fact that they are grouped hides access to their text? I am using Excel 2003.
I have managed to code a workaround, but its performance is atrocious (involves shaperanges, ungrouping, etc..)
I have daily data that has columns for day, day of week, week end, month, year. I am trying to create a pivot chart that displays the data as a line chart day by day and as a secondary axis column chart by week.
View 1 Replies View RelatedI have sheets with over 40000 rows. I grouped them in 60 and I need the average for every group on the 61st (which is blank). For example, when collapsed I have visible the following blank cells:
A61
A122
A183
A244
and so on.
What I want is to enter a formula in A61 for the average of A1:A60 then drag down and have the correct values in all the rest (average of A62:A121 in A122, etc.).
Example:............
Where all question 1 fields are put into a row, 2 in another row, etc (this may be more than 2 sets of questions), grouped by the name field. I have had a look at Pivot tables but they dont seem to do what Im after.
This is the data:
jan09 feb09 mar09 .....
Division 1
Division 2
Division 3
Division 4
Division 5
jan08 feb08 mar08 .....
Division 1
Division 2
Division 3
Division 4
Division 5
The graph would be a bar graph with 2 bars next to each other per month. each bar would be stacked by division. The goal is to compare for example sales in 08 with sales in 09 showing 2 bars next to each other split by division.
I have a workbook (Sort Test.xlsx) where multiple rows belong to the same "group". Is there a way to sort by a value (e.g. name of the test, date of the test, etc.) while maintaining the formatting and keeping the "groups" together? There are 3 sheets in the work book. The first shows the sheet as is, the other two are examples of how I would like to be able to sort the data.
View 2 Replies View RelatedIn my pivot table I have "Due Date" with is a column label.
I grouped the dates into weeks by using the "days" grouping and using 7 days.
I created a slicer to allow the user to select with week they want, but the slicer is sorted as though it's a text field and not a date field.
Example:
6/1/2015 - 6/7/2015
6/13/2016 - 6/19/2016
6/15/2015 - 6/21/2015
6/16/2014 - 6/22/2014
I want the slicer to sort by actual date.
I want to create grouped labels on a chart x-axis of the type created automatically when creating a pivot chart. For example: I have projects, samples and sample yields. There are multiple samples in a project so I have a sheet that looks something like this below:-
Project,Sample,Yield
2006ANG01,CTY001,1%
2006ANG01,CTY002,1.5%
2006ANG01,CTY003,1.2%
2007ANG03,CTY456,2%
2007ANG03,CTY457,1.9%
I would like to create a chart that has Project and Sample as the x-axis where the samples are all grouped into their project. Pivot charts do this nicely, but how to do it without a pivot chart? As you will see from the attached file I can get both Project and Sample on the x-axis, but the Projects are overlapping.
