(and about 5000 more sets with garbage page headers in between the sets of parent rows)
Basically the data spit out by machine is separated by a blank row.
Let's say I need all the data with the child row "tn" , so I would autofilter /PivotTable it for that entry, but that leaves me with a blank column A. It'd be no issue if I had the column A heading in the child rows (and the number of spaces in between is random like 2 sometimes 15+ so Vlookup/Index/Match aren't too useful). It is possible to have a function that repeats the first row until it hits a space?
I have the following table and i need to be able to present it an indented list of parent child relationship. The hierarchy can go 1000+ levels deep. How do i achieve this using VBA?
OName OPackID PPackID PName ParentID
Name1 556 556 Box1 389
[Code] .........
If there is a better way of presenting it, it isn't limited to an indented list.
I have data for , say - Cars, Company and Average.
I solve this upto 2 level i.e cars and companies.
But i want a third level also.
When i select Car from drop down list, using data - validation - list , in cells(1,1) , i should get all companies in Cells(1,2) and on selecting companies , i should get Average in cells(1,3).
If i do define name ,and then data validation list , and then use Indirect() function, i can solve it upto 2 level.
Eg. I can get company for car .. but how to get Average Also.
I have an excel file (see sample attached) with a hierarchy with parents and children spread across different columns and rows. However, I am trying to condense it into one column with parents and one column with children. The only way I've been able to do this is by copying and pasting parents onto children rows, which is both a manual and time-consuming process (when you have a lot of rows). I feel like there must be a way to use VBA or a formula or something to get it to work. I have attached a sample, so you can see what I am trying to do (current data and desired data).
I have searched numerous times for the answer I need, but no joy yet.
I have a spread sheet that has one column (call it 1) of data with a single entry each of 100, 200, 300, 400, 500, and so on.
I have a second column (call it 2) that has multiple entries of 100, 200, 300, 400, 500, and so on, and I want to sort so that the rows wind up with the numbers in column 1 all sorted in an accending order, but I want all the same numbers in column 2 sorted to "list" next to the same number before the next number sorts. See below for a small example of what I hope to see once it sorts. 100 100 100 100 200 200 200 200 300 300 300 300
Auto Merged Post Until 24 Hrs Passes;Unfortunately the example didn't post correctly..perhaps this will work but note that the x's are in column 1 and don't exist.
I'd like to hide all worksheets and in the excel there is only one visible sheet which is Navigation Page. I want to add several buttons on this sheet and each button will be clicked to call the other sheets.
For example, there is a sheet called Sales_Approval_Form and I'd like to add a button on navigation page called Sales Approval Form and when the user clicks the button the sheet will be open for entering data.
I have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.
In my excel file, I have a cell with a hyperlink which points to an intranet portal. When I copy the hyperlink and paste it in the browser's address bar, the site opens. But when I directly try to open from the excel, by clicking on the cell, it redirects to a wrong URL and eventually does open the site.
I'm writing a program on VBA for Excel and I've succeeded in just displaying the Userform when opening the file. I have set the ActiveWindow.Visible option to False and the Application.Visible one to false as well so that i only have the user form displayed.
However, if i try to open an excel file via "My Computer", the file doesn't open. I also noticed that when I was leaving the application.visible option to True, i couldn't access the application while the userform was running. I think i need to do something with an event, but i can't find the event corresponding to opening a file oustide Excel.
I keep getting an automation error in excel 2010 when I click on the button that executes the macro. Excel crashes when the box appears. There is no code in the error box. Why I get this error message.
I've included the code I'm using to calculate the net weight depending on which option button is selected. I use a flag ("VLAG") to know whether it's the first time the button is pressed. All variables are DIMmed at initialization (different sub).
By using other flags, I can see the code runs perfectly, but the tare weight ("TARRA") is not incremented each time the button is pressed. The formula I'm using to increment is
I want to make it possible to refresh a particular sheet on clicking a button. i can create the button and even assign the macro. but the code for macro, to refresh i don't know how to generate the code.
I have two buttons in my excel ("Fund Set up" & "Broker Set up") sheet1. so what i need is when i click the button named as "Fund set up" a input box should appear as "Enter the volume" and the user has to input the volume..For ex, user has entered the volume as 2. After that a line of data should be populated automatically from col A2 to col E2 as below.
Similarly if i click the "Broker set up" button the same action should happen but the activity should be populated as "Broker set up".
S.No Date Activity No.of Req Type of Action 1 21/01/2014 Fund set up 2 Input 2 21/01/2014 Broker set up 4 Input
So whenever i click any of this two buttons a input box should appear to get the volume and once the volume is entered by the user an entry should be populated as above in a new line.
I have master data in sheet1 with 4 headings (EmpNo.; Name; Grade; Status) and i put a push button beside it. what i want to happen is that, if i push the button, all data with a value of "Inactive" in sheet1 will be COPIED to sheet2 with the heading but only the EmpNo and Name will be transfered to sheet2.
how can i define, in vba that after click on button in sheet1, it looks at value in cell B1 in this sheet and move my cursor exactly on this value in sheet2 where i have data in range (B4:C6000).
I have a file having around 57 sheets.But here,I have taken a example.I want a file whose show me only summary sheet.But when,I click the cell no.C7 then the related sheet should be open i.e.FNDADRSCC,and all the sheets should be hide.I have little knowledge of HYPERLINK formula but not to the desired stage.
For more clarification, refer attached file.i.e. BOM Sheets
Desire result file have attached also i.e. Excel_formula
I have a problem on auto-sorting my data after clicking the "x" or "Close" button on DataForm. Im creating a command button that trigger a macro to open an autofill DataForm. I want a code that everytime i ADD or EDIT using the DataForm, it will automatically sort the Column A after clicking the "x" or "Close" button. Below is the code of my macro:
Code: Sub OpenForm() Sheets("DATA").Select ActiveSheet.ShowDataForm End Sub
i use excel 2007, it s slow to open a file by double-clicking and entering from windows explorer, by googling i fould a fix by adding "%1" at the end of command in "Application used to perform action" (folder options/file types/xls/advance/open). however, to open files in the same instance, i have to keep "DDE message" with [open("%1")]. With both "%1", excel now opens twice. How to fix it?
I have a worksheet where I created a button for the user to click on when they want to generate the chart, but when the button is pushed, it goes to my code in the VBA editor and gives me the error I've attached below. I have no idea what that means and none of my code seems to be highlighted.
Due to the protection function, users cannot sort by descending (using filter), and this is causing some problems. So I was wondering if it were possible for the sheet to be sorted automatically whenever the spreadsheet is opened. Is this a viable solution?
The sheet is titled Summary (Sheet 1), and the range is A4:CI301 and I would like to sort based on the data in column C, in Descending order (C4 - C301).
Like I said, I'm not sure if this is the best method or not, so I am open to any suggestions that may be applicable.
I have tried to open an internet page usin excel in order to get some information, for this I wrote the following code which doesn't work:
Dim i As Integer For i = 2 To 3000 Workbooks.Open Filename:="http://www.anm.ro/anm/anm_maint.asp?anmPage=2&ID=i" Application.Goto Reference:="R12C3" Range("C12:C25").Select Selection.Copy ActiveWindow.ActivateNext ActiveCell.SpecialCells(xlLastCell).Next.Select ActiveSheet.Paste ActiveWindow.ActivateNext ActiveWorkbook.Close Next End Sub
How can I make so that, this procedure to open page with ID 2, and 3and so on
I have a sheet that I would like to print to one page (11x17, landscape) and have it take up the majority of the page. The largest the sheet will ever be is data in rows 1 - 71, and columns A - AC. Right now I have it set up so that when i click on the button, the print dialog opens up and no adjustments are needed to print to pdf in 11x17 for the maximum amount of data.
Overall what I am trying to do is have the zoom change when rows are deleted (columns will always be A-AC). The top 5 rows and bottom 5 rows will always be there. I want the top and bottom margins to be the same on the pdf even if 10 rows are deleted in between.
However, when this is executed it returns the following error:
Run-time error '-2146697210 (800c0006)': Method 'FollowHyperlink' of object '_Workbook' failed
Clearly, I'm doing something wrong. I wondered if it had anything to do with the fact that the webpage I'm trying to open is a pdf and not html or similar, but this doesn't appear to be the issue.