Group / Ungroup - Tree Structure
Mar 6, 2014
I am trying to create a sheet to resemble tree structure of rows. That is by default all the parent rows must be visible and click of + sign, the row should expand to show its child rows which in turn can have child rows but appear collapsed. When I choose two rows and click on Group and select rows, I see a grouping but when I try to collapse using - sign, both 2 rows gets disabled. I want to make the first row as parent and second row as child. How do I do it?
View 1 Replies
ADVERTISEMENT
Jun 19, 2014
I was sent this file and I want to replicate the bill of material tree structure on the left. I'm not sure how it is done.
Image: BOM_example.png
View 1 Replies
View Related
Oct 1, 2008
I encounter the following problem which has been bugging me for weeks.
I am doing up an engineering spreadsheet which calculate the electrical cable size from node A to node B.
Col A and B goes like this:
From|To
Transformer|MSB_A
MSB_A|SSB_A1
Transformer|MSB_B
MSB_B|SSB_B1
SSB_A1|DB_A1
SSB_B1|DB_B1
So Transformer is the parent of MSB_A and MSB_A is in turn the parent of SSB_A1 and so on.
What I would like to achieve is to put these data into the following configuration:
Transformer----->MSB_A
............................|-> SSB_A1
.....................................|----->DB_A1
Transformer----->MSB_B
...........................|-> SSB_B1
....................................|-----.>DB_B1
NB: Please ignore the "dots" above.
This will give the user an overview of the parents and node relationship.
I can't seem to find a solution how to achieve this.
View 9 Replies
View Related
Jul 28, 2009
I have an organizational hierarchy dump in excel from an SAP ERP system. The format of that file is less than user friendly and I need to convert it to a flat file format so I can do pivots against it or possibly dump it into access database. The background is as follows (I am also attaching a file).
The organizational structure is multinoded/layered. Top most node is a company second from the top are divisions designated by 02DivName each 02DivName has multiple nodes assigned to it. The lowest level of the hierarchy is a department, always a numeric, always same length.
That's where it gets interesting. Each node/layer under 02DivName has a technical description and text description they get broken up into two different columns when SAP dumps them. Technical descriptions can be used to identify where in hierarchy the node falls(parent/child). For example 02A is a child of 02DivName 02B is a child of 02A 02C is a child of 02B and of course all of them roll up to 02DivName. There could be multiple 02A's under Div Name and their respective 02B's, C's D's would roll under them. So on export file it would be graphically represented 02A1 blank blank blank blank in that column and then change to 02A2 their respective 02B's would be in different columns works the same for C's and D's.
Here is another wrinkle the department is not always assigned to the lowest level grouping. I could have 02DivName dept, dept, dept, and then 02A group assigned to 02DivName. The department numbers end up on export file in the same column as fre form text descriptions for the nodes. It is staggered format.
The object of what I'm trying to do is basically convert the gibberish described above into flat file format via a vba script. I need to have node1 node1 desc node2, node2 desc, etc etc in columns the last column being a department number. So if for example I have a dept assigned directly to 02DivName directly all the other node descriptions will be blank if it's node 02A then the node1 and node2 will be populated. Any help will be appreciated. And here is the file with excel spreadsheet
View 5 Replies
View Related
Dec 15, 2011
I am having a problem with one of my workbooks. I have grouped some columns, but now I can't see the button on the top of the grouped ones. I tried to group/ungroup again and, as there were some hidden rows, I tried to unhide them all and it is still not working.
View 2 Replies
View Related
Jun 12, 2013
I have put the following code into my excel workbook to allow me to ungroup sections in a protected sheet. How can I change it to specify multiple worksheets?
Private Sub Workbook_Open()
With Worksheets("P5")
.EnableOutlining = True
.Protect Password:="rob", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub
View 4 Replies
View Related
Oct 18, 2013
I have a ton of worksheets that I maintain for Budget, LE, LE2, LE3. Every time we complete a Budget I need to group the LE Columns, and once we complete an LE, the budget column needs to be grouped and LE column ungrouped. So twice a year I need to make these changes. It is quite cumber some to change this for multiple tabs.
I already have a technical worksheet to dynamically change the date, naming etc. I want to be able to add a cell that will either read 200 (budget), or 300 (LE). Depending on what his cell reads, I want to group and ungroup their respective columns.
View 2 Replies
View Related
Jun 1, 2006
I'm building a template for various users. I have protected the sheet with only certain cells available for input. The file is very large and there are some sections that can be group/ungrouped as the user desires.
however, once I protect the sheet, they can no longer click the - or + to expand and collapse. How can I enable group/ungroup on a protected sheet?
View 9 Replies
View Related
Dec 19, 2007
Can any Mac users out there confirm that if a sheet is protected that a user cannot group and ungroup rows using the "+" if the sheet is protected using the following...
With Sheet1
.Protect Password:="******", UserInterfaceOnly:=True
.EnableOutlining = True
End With
It works fine on my PC Office 2003 but I hear it won't work on a Mac but I can't confirm it.
View 3 Replies
View Related
Jun 1, 2014
Once I "protect sheet" , i can not group / ungroup the column / row. after protecting sheet, I want the freedom to group/ungroup the column.
View 2 Replies
View Related
Feb 21, 2014
What I need to do is group and collapse certain columns when Cell D4 in my Date tab reads Bud. If it doesn't read Bud, I need it to ungroup those columns and group and collapse other columns.
So for example: If I have 6 columns (A - F), when Date tab cell D4 reads 'Bud'. I want to group columns A and B, and only show C-F. If the cell doesn't read Bud, I want columns A and B to be ungrouped, and Columns E and F to be grouped and collapsed.
I've been racking my brain, using the Record Macro and just cannot get it correct. Granted I have created something similar to HIDE my columns, however I need it to be grouped so that higher management can ungroup to see some details if need be.
Code:
Sub GroupColumn()
Dim s As Worksheet
For Each s In ActiveWorkbook.Sheets(Array("Name1", "Name2, "Name3", "Name4"))
If Worksheets("Date").Cells(1, 4).Value = "Bud" Then
[Code].....
View 5 Replies
View Related
Aug 20, 2008
I used some code to allow grouping/ungrouping in a protected sheet. This works great! Enable Outline/Outlining on a Protected Excel Worksheet. However, when I share this protected workbook, all of a sudden grouping and ungrouping is not possible anymore. I get the same error as before, when the workbook was protected, not shared, and did not have the code mentioned above. Error: "You cannot use this command on a protected sheet. To unprotect..." Assumption: Is it possible that the Workbook Open Event is not being triggered if the workbook is a shared workbook?
Question: How can I group/ungroup columns in a shared (and protected) workbook?
View 6 Replies
View Related
Dec 20, 2007
We have a file that needs to be updated monthly by running macros, but the macros are being blocked by protection. I would like to put some code at the start of the macro that unlocks all sheets and then at the end locks them up again. Actually, all I really need is a macro to do just that unlock all sheets, and then another one that locks them back up again as we will need to lock and unlock at various times and it is annoying to have to do it by sheet ....
View 14 Replies
View Related
May 6, 2009
How do I make the following conversion :
(Column values are separated by =)
I'd appreciate if someone can help create a Macro for this. This might have a long list and a longer tree as well.
Input
========================================
column1 column2
---------------------------------------
1 = top
1-2001 = middle
1-2001-2002 = bottom
1-3001 = alpha
1-3001-3002 = beta
1-3001-3002-3003= gamma
output
========================================
column1 column2
---------------------------------------
1 = top
1-2001 = top-middle
1-2001-2002 = top-middle-bottom
1-3001 = top-alpha
1-3001-3002 = top-alpha-beta
1-3001-3002-3003= top-alpha-beta-gamma
View 9 Replies
View Related
Jan 26, 2007
how to create a collapsable tree in Excel using VBA.
Note: There is restriction that I cannot use ActiveX
A small example will be very useful.
View 3 Replies
View Related
Dec 9, 2009
I've been trying my best to create a directory structure using VBA and Excel, and have run out of patience on how best to do this. My goal is to create a directory tree containing 2 levels. The 1st level (Main Folders) will be named from data in Column A of an Excel Spreadsheet, the 2nd level (Subfolders) will be created from data in Columns B, C and D of the spreadsheet. I've been using the following code with success, but don't know how to create subdirectories:
Sub StartHere()
Dim rCell As Range, rRng As Range
Set rRng = Sheet1.Range("A1:A20")
For Each rCell In rRng.Cells
CreateFolders rCell.Value, "C: est"
Next rCell
End Sub
Sub CreateFolders(sSubFolder As String, ByVal sBaseFolder As String)
Dim sTemp As String
'Make sure the base folder is ready to have a sub folder
'tacked on to the end
If Right(sBaseFolder, 1) <> "" Then
sBaseFolder = sBaseFolder & ""
End If............
View 5 Replies
View Related
Nov 19, 2006
My Question: how would i be able to render a dynamic drop down tree in the following format (see attachment): look at the left Rich Graphics folder example:
http://javascript.cooldev.com/script...mos/superdemo/
View 3 Replies
View Related
Dec 5, 2006
I want to get a tree view created with the following details
NCR SUMMARY
KISHOR ASRANI
JAYDEEP SINGH
HITESH MADAN
LALIT MONGIA
ANISH BHAN
SUMIT WADHWA
VIBHOR DEWAN
SANJAY DHAR
KIRTIKAR OJHA
PANKAJ KHUSHANI
GUNJAN SACHDEV
SHUBENDU GHOSH
RON SUMMARY
SANGEETA BHATNAGAR
ATUL PAL
HARSH MALHOTRA
SALIL KHANNA
EDIT: FYI: thomach tried adding code and then quote tags to see if the indenting showed. Neither worked.
View 5 Replies
View Related
Jan 8, 2007
I want to create sections in excel exactely like below. I need to show user how many types systems are available and in each type how many config systems available.
View 4 Replies
View Related
Aug 31, 2012
I am trying to build a cup tree that automaticly move players over to the next round if they are first or second in
their group of 4. What happens when first person have a result of lets say 33 and 2nd and 3d person have a result of 35 each and the 4th person 37? First person is easy but then there are 2 persons with the sam result and they play sudden death and we put in a 1 in the sd column for the winner and a 2 for the loser of the sudden death.
How do I make my formula figure that if they had the same result go look at the sd column?
Another thing is that of the 2 players advancing they have to be placed in the same order in next round, how can I achieve that?
If you look in column I row 19-22 there are players in the wrong order because the player with the better result goes in first. It should be this order: 9 11 15 16 instead. (if you are ahead in the previous round you will be ahead in the next round)
Another problem I noticed now is that when 2 players has the same lowest score the first player takes both spots as it is now and that doesn't work in real. You see what I mean if you look at player 1 in round 1 and 2 (R1 & R2), in this case it should have been players 1 2 6 and 7 moving on to round 2 and not 1 1 6 7 as it is at the moment.
(Rank is their rank from the qulification rounds where the first 4 is seeded and the rest is drawn into 1 group a time)
Klass B
*
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
2
Rank
Name
R1
sd
Pl.
[Code] .......
Spreadsheet Formulas
Cell
Formula
I7
=IF('Klass B Calculations'!F4=1;'Klass B Calculations'!C4;IF('Klass B Calculations'!F5=1;'Klass B Calculations'!C5;
IF('Klass B Calculations'!F6=1;'Klass B Calculations'!C6;IF('Klass B Calculations'!F7=1;'Klass B Calculations'!C7;""))))
[Code] ..........
View 4 Replies
View Related
Aug 1, 2006
I'm trying to write a macro that will build a tree from 3 sets of lists and I'm really stuck!.
The thing is:
1. I have 1 worksheet with 2 sheets. First sheet "Lists" contains in every column a list of data.
2. Range("B:C") contains list od SERVICES1 - every value in list has its corresponding ID_SERVICES.
3. Range("D:F") contains list od SERVICES2 - every value in list has its corresponding ID_SERVICES2 and a parent id ID_SERVICES.
4.Range("H:I") contains list od SERVICES3 - every value in list has its corresponding ID_SERVICES3 and a parent id ID_SERVICES2.
5. Second sheet "Project" in Range("A:C") contain example of tree like: ...
View 3 Replies
View Related
Oct 7, 2006
I have a data validation list in B31 "material type" that has 4 options (Adhesives, Metals, Backer Board, & Grout). Based on that selection F31 needs to display a list of "descriptions" that match that "material type". The list is in book "database.xls!sheet3". Material type list is Column A, Description is in Column E. I know that you can't link to an unopened workbook in data validation, & I've been trying to code it, but again the lack of expierence & knowledge has frustrated me again :P This code to get the info for the file to open
Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) <> "" Then path = path & ""
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function....................
View 9 Replies
View Related
Mar 9, 2014
I've Sheet with Group off rows i want to ungroup All rows and do some calculation then
View 1 Replies
View Related
Feb 23, 2007
I need code to do this: When I select a range of cells, say E20:H24 then hit a button the code behind the button would ungroup all the shapes in that area. So if I have grouped shapes elsewhere, they would be uneffected.
View 9 Replies
View Related
Sep 4, 2008
I don't do a lot of VBA, so this should be really simple, but I'm going to defer to the experts! Thank you! I often need to group tabs in Excel 2007, but oh the horror if it saves while those tabs are grouped and someone opens it up and starts typing! Is there a piece of code I can enter that will ungroup all the tabs before the file saves?
View 9 Replies
View Related
Jan 30, 2007
Is it possible to ungroup all rows using a cell click instead of the default option that excel gives?
I know this seems a bit pointless but i submit my excel spreadsheets to many people that do not notice the plus & minus signs that excel creates & misses the hidden data.
This way would allow me to put a simple Open / Close text in a cell that would be obvious to click on.
View 4 Replies
View Related
Oct 21, 2007
"Enter a formula that adds the total gross sales for the first quarter in cell B6 in the Q1 worksheet and total gross sales for the current quarter in cell G18 in the Q2 worksheet"
**First ungroup the grouped worksheet
How am i going to formula a total gross that i don't even know what is the first quarter? i don't see anything for first quarter? and in Q2 worksheet G18 i already did =SUM formula Ungroup the worksheet? i don't think my worksheet is even group?? how i ungroup it?
View 14 Replies
View Related
Mar 17, 2014
I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
View 11 Replies
View Related
Nov 12, 2009
I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
View 9 Replies
View Related
Feb 3, 2012
I need to totally ungroup existing grouping of rows in a sheet. Totally ungroup = strip it totally of any grouping. In short, it should be back to its original state of no grouping at all.
Problem is that I do not know if the sheet has existing grouping, or if it does, how many levels of grouping.
The solution I have in mind right now is just to indiscriminately run ungrouping vba line 10X and just place an error handler i.e.
Code:
Sub Macro1()
On Error Resume Next
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
[code].....
Is there a shorter way to handle this code-wise?
View 3 Replies
View Related
