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.
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 RelatedI'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............
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
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
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.
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/
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 View RelatedI 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] ..........
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: ...
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....................
Excel 2007 - need to adjust protected view settings but the protected view tab is missing from my Trust Centre settings - have the following tabs - trusted publisher / location, add-ins, activeX, macro, message bar, external content and privacy options. Document is a revenue authority download and without being able to adjust the protected view settings, can't input data.
View 3 Replies View RelatedI 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.
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
if there's a way to either define what columns or rows are printable or to somehow leave columns (or rows) as visible within the print area but omitted from printing as if they were hidden.
In my CAD package I can simply elect to have certain collections of information "non-printable", is there any way I can do this with columns without having to rely on people managing to laboriously (sic) hide and unhide columns either side of printing a document.
I've design a small little excell program that brings up a form and hides the excel workbook upon opening. By using the form the user is able to input data to a worksheet in running in the background. I've added a button on the form where I want the user to be able to open the worksheet which is password protected and view or print the data. I've already got the code to hide the workbook, protect and unprotect the sheets, I just can seem to get the code to view the one worksheet.
View 3 Replies View RelatedIn keeping with their monopoly on code, Microsoft has, of course, locked all of the included XL add-ins (the code part).
I was interested in looking at how some of these add-ins functioned, seems like it could be fairly educational.
how to unlock the modules that come with XL's add-ins?
i was wondering how i can get excel to hide everything except for the worksheet, as soon as it starts and then put it all back when you quit.
View 2 Replies View RelatedI am using some protected workbooks and cannot see the tabs at the bottom of the screen.
I have tried going to tools/options/etc
I have tried viewing full screen
If I copy the current sheet 1 and paste it into a new workbook I can then see tabs (but only sheet 1 has copied data).
I use a two monitor setup driven from a Radeon 9550 graphics card, but the problem I have occurs on a single monitor as well. When I open multiple workbooks or worksheets I can only view one at a time the other remains embedded behind the open window. Nothing I know of will let me view both at the same time.
If there are two open worksheets full size on the screen and I minimize one they both minimize. If I decide to close one workbook/sheet down all open workbooks/sheets close down as well. Can I view two workbooks at the same time? Can I close one down while keeping one open?
Ive seen on someone elses workbook the first 6 columns are fixed (always in view) then you can still scroll across but those first six columns always stay in view.
View 3 Replies View Relatedi was doing something in Tools -> Options -> View, & suddenly the dates in my sheet disappeared & some numbers came up, i can view the date in the formula bar, but can't be viewed in the cell, & i am also unable to do the alignment for the cell.
View 3 Replies View Relatedsee attached WB.
Preferable solution using only Sheet Formulas only.
I'm stuyding the book "Professional Excel Development" which has a Project Template and an addin with several modules and a worksheet. I can view the addin's modules but not it's worksheet - is this normal for addin's? I haven't worked with them much before.
View 2 Replies View RelatedI am building an application through Excel to update specific internal website information. My question is, is there an easier way to identify and view the tags on a web page without having to right-click and "view source"?
View 2 Replies View RelatedHaving trouble setting VB windows views back to deafult ...
View 14 Replies View Relatedi'm having issues with my macro, and trying to view the string in a msgbox ,but having issues doing so:
how can i view strSourcePathname in a MSGBOX?
Code:
Sub CopyPaste()
Dim strSourcePathname As String, Report As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
[Code].....
I work With Excel 2003
When Creating Data Sheet And Making Pivot Table
When I Make A right Click On The Pivot Sheet
The " View Code " Is Deactivated
As In the Pic
i am trying to use this code to view the workbook (WK) in the userform named WK
it can not reconize .spreadWK
Private Sub UserForm_Initialize()
Me.SpreadWK.Cells.Range("A1:E10").Value = ThisWorkbook.Worksheets("WK").Range("A1:E10").Value
End Sub
however this is saying that it can not reconize
.spreadWK
I have this data table where the totals are in the last(bottom) row.The number of rows of the table increases as new records are added to the bottom of the table. Now, the problem is , I need to have the Row that contains the Totals alwas on view no matter how big the table gets and no matter how far down/up the workshhet is scrolled.
I have tried Freeze Panes/Spliting adding new window , live shapes etc but all look dodgy and bring their own set of problems.
I have also thought about putting the Totals of the table in a row at the top of the worksheet and freezing the panes at that location but having the Totals showing at the bottom of the Table is the normal intuitive way .