I *think* I know the answers to this, but we'll see...
Let's say I have five salesmen: Mr. Red Dr. Green Ms. Blue Mr. White Mr. Black
Each month they make sales.
So I have a table, where each salesman is a row and each month is a column, and the cell is populated with sales.
I'd like to do a COMBINED Horizontal Bar chart, where: - Each row is a month - The size of each person is actually their PERCENT of Sales, not the total amount (without me having to add rows of calculations) - Each month automatically sorts the colored boxes in size order.
In other words, here's sample data: Red - ( 0, 3, 0, 9) Green - ( 8, 3, 6, 5) Blue - ( 7, 3, 2, 8) White - ( 4, 2, 3, 7) Black - ( 5, 5, 5, 5)
So the top bar would have four colored boxes, in the following order: Green, Blue, Black, White The green box would be 33 percent of the total width, the blue one just a titch smaller, and so on
The next bar would have five boxes, and would be the same length as the one above, and have, in following order: Black, Red, Green, Blue, White The black box would be just smaller than a third (5/16 of the length); red, green, and blue would all be the same size, and the white box would be a titch smaller
The next bar would also have four boxes (time for Red to go!), and would be the same length as the ones above, and would have, in the following order: Green, black, white, blue The boxes would be sized appropriate to their ration in that row (ie, even though Black has sold five each month, each month it makes a different proportion)
and so on.
A doughnut chart does the percentage-of-total thing snazzily, but the sorting gets weird.
I actually havve a ton more data than in my example, so manual calcs and manually manipulating won't work here.
I've been tasked with entering a list of contacts into our database. The list is pretty long, so I want to upload it all into our dbase by reorganizing it all into a csv file. I need fields for name, title, organization, address, phone number, etc. problem is not all contacts have info for all fields. I'm having a hell of a time getting this data into a usable format.
I want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.
The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).
The following columns will be exported from Generated Report -
BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.
BRIDRequirement Bug DescriptionBUG ID Alt Bug ID PQ115 PQ115-Login Bug Description text xihllloloj43712 123 PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126
For Matrix
The data will be imported into Matrix worksheet into the following columns as part of the row headings
- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data
The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.
I want each section (marked by BRID number)to be separated by row headings.
These row headings are to be generated after populating all data within each BRID number.
When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.
The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath
BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do
BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do
So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.
i have soccer table 1 sheet is "teams" which is the table of the league. 1 sheet is "games" which is the table of games of the league the last sheet called "import by date". in the end of every game day i sent email with the results of current day. for now im copy and pate all results and its not that easy and convenient. i want to make some formula that when i click in import data sheet the date its shows me all the results from that date. i try to do it with pivot table but didnt like how it displayd
I have a tedious task of copy/paste from our website. have a look at below sample data. I have around 1500 rows of data like this.
Sheet1 A B
[Code]....
Column A has input data which i need to copy and paste in a web form. Column B will have results scrapped form webpage[ URL]. This webpage will need login details
If a code can be written which take value form column A of spreadsheet and paste in webpage like shown in above image and hit search button. Next webpage will be loaded with number of results like shown in below. Take the number of results and paste in column B of spreadsheet.
I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.
However, I have a problem in that my import macro gives me 'Run-time error '1004:
Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!
The code below shows is for the import macro only:
Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer
I'm wondering if I can use external data in an excel spreadsheet without having to import all the data. I basically need a persistent connection with the external data and I want the excel spreadsheet to get the item descriptions by using the item number as the user types in the item number. Writing out the descriptions is killing me and I have all my products in an excel spreadsheet already. I really need someway to link two separate excel files together. One with data and one with user input that searches that data as I type in the item number.
I have a tab in my workbook with 10 various items 50 pieces each, in the 2nd tab i have about 100 orders for these items. What is the best way for me to find out for each of the 10 items how much i sold?
The task that I have to make is a little bit complicated, especially for reason that I am not v VBA programmer. But however here is the task (and is not a school assignment).
First I have to clear the worksheet Invoice_Plan (file Financialplan.xls), but the whole row(s) that have data in column F (comment) have to stay on worksheet.
Than I have a worksheet Data (file invoices.xls where are all invoices from year 2009) and in column A are the data, that I have to import into in worksheet Invoice_Plan to (second file FinancialPlan.xls) in column A data under following rules (other must be skipped):
there is i website that i want to imoport data from it to my excel sheet using vba, but the poblem is that the website will need my username and password...
i search around for possible threads and i found a very close one but couldnt have it do the submit option. also i donot want it to show me the intenet explorer window. i just wantit to do these steps: after clicking a button:
1-logon to the site using my user and password 2-redirecting to the report link and improt what ever is in that page 3- all this with out opening IE.
Public Declare Function ShowWindow Lib "user32" (ByVal lHwnd As Long, ByVal lCmdShow As Long) As Boolean
Private Sub LoginTone()
'Dim ie As InternetExplorer 'Set ie = New InternetExplorer Dim ie As Object Set ie = CreateObject("InternetExplorer.Application")
With ie .Navigate "http://s8.brono.com:80/mongol/fiona/index.php" ; this is the login address Do While .busy And .readyState 4: DoEvents: Loop.......................
I want to create a VBA that import data from another workbook. when i run the Macro it give me the option to choose the file and when i select the file then copy data from sheet 1 (A1:D1) and paste to my active worksheet. I do not want the other file open because it is too big and takes fro ever to open it.
I am trying to import data from an external source file (Excel) into an Excel sheet that will get uploaded into a db accounting system.
The source data varies month to month in the number of rows and need I to import 7 columns.
The upload file will be saved as a text file for the upload. I tried to use Import External Data, but since the rows vary in size (anywhere from 1 - 2000 rows) I had to create a named range for the entire column. Upon saving this caused all rows beyond the last row of data to have quotes in them which will kill the upload. Is there a way around this with the Import External Data, or is there a better way through some combination of INDEX, MATCH, INDIRECT or the like?
I am trying to import data from web to excel. I posted the link below to the website I am attempting to import from. The only part I need is the statistics. However, I cannot get it come thru by getting external data from web.
The goal is to great an auto updating spreadsheet with multiple sheets so copy and paste is not feasible.
I am trying to import data from different spreadsheets "about 100" to a principal spreadsheet which will have all the centralized data. I included the principal spreadsheet layout and a sample of two detail spreadsheets. The information that I want to import is from column B 1 to 5 from the detail spreadsheets and put into the principal spreadsheet from row D to G.
Is there any way to import this information automatically, like creating a macro or something similar.
I have a workbook that contains an 'AllData' sheet. That sheet has 6 columns titled "category, keyword, bid, advertisers, search vol, online". Each category has its own worksheet and i want to put the data for each category onto the corresponding worksheet. I am having a difficult time describing this so I have attached a brief example. I have to do this for 145 categories so I would much prefer to do it automatically rather than manually.
I have several of the attached files that come in every week for me to process. I would like to be able to put all of them in a folder and have the highlighted columns copy to one master file and eliminate all the extra blank rows. I also need it to sort by salesman with all of their sales in the different catagories grouped together. I am not sure if this can be done given the way the salesman's names are not on each line with their sales.
Each day I copy paste data onto a sheet (below the prior days copy paste). What's a simple code to automate this? I want to keep all prior day data and just paste into the first blank row
I have several files from different departments that I have to extract sections of data from and compile into one central file for reporting.
I am very new to VBA but would like to set up a button so that I can import data from closed files in different departmental directories. I have recorded a macro and got it to work if the file is open but I can't seem to get it to work if the file is closed.
I've just been task to create a VBA script macro button in Excel so it automatilly pulls data from my SQL database. Can anyone show/point me to the right direction on how to accomplish this?
I'm trying to pull in data from another Excel workbook which gets updated on a regular basis. Using the Import External Data fucntion in Excel I created a query which pulls the data from the original workbook into a tab in the workbook I'm using. The issue I'm having is that not all of the column headers get pulled in when using the Import External Data function. I tried typing the column headers in myself but they disappear again after refreshing the data set. Any ideas what is causing the issue?
I am trying to import the data on the url below. When I go to the external data inport the page is loaded and I click the indicator to load the central table but when the data loads the page is severally corrupted. I have found this on a few pages but some pages load correctly.
I have a TXT file that is properly format for a nice print out but not in any fashion to work in an excel or access file.
My txt file looks something like this Header info Row 1 Header info Row 2 Header info Row 3 ACCT# Value Descripton ~ Part number Customer Name ~ Vendor Name Customer add1 ~ Vend add1 Customer add2 ~ Vend add2 Custom city, st, zip ~ vend city, st, zip
Repeat about 5 or 6 more entries do a page bread and repeat the headers and then 5 or 6 more entries
I want to automatically copy the entries and place the data into Row 1 of Excel starting in Column 1 through Column 12
I'm trying to import data into my sheet with formulae like this in row1(Columns A:I): ='F:Dir[File.xls]Sheet'!A2.
The files I'm importing the data from all have 50.000 rows and 9 columns, so I copy the formulae downward 50.000 times. This generates an error ('Not enough memory'), not surprisingly. I've also tried to do it in steps by way of VBA; first two thousand, copy and paste values, next two thousand etc. But that doesn't work either.
Does anyone know of a solution to this memory problem or a way to work around it (without opening the files with the data in them).
I’ve got a question regarding import of external data in excel. The workbook I am conceiving at the moment does have a database query from another excelfile(a report from my business system) via the standard Excel functionality and it works perfectly fine. What I can’t figure out is how to re-direct my query to any file/path I choose.
At the moment I just replace my original file with a new freshly updated one but I am stuck with the original path and filename or else it won’t work.
I am having some issues importing data from Excel to Access. I am attempting to import about 45000 rows of data from Excel to Access. It appears Access is only importing about 16000 of the 45000 rows. I am using 'import' in 'get external data'.
Is there a way to have Excel retrieve updates from a Web Site? Specifically what I am interested in getting is flight schedule information from sites like USAir, JetBlue, American or Other carriers. I have a list of Dates and FLight numbers in a spreadsheet, I would like to get updates on Arrival, Departure or status ( Delayed / Cancelled etc ). The Excel sheet would provide Date, Carrier, Flight Number, Departure Point (if Needed ), Destination Point ( if needed ) and flight Departure and Arrival Times. I would want to pass this data to the appropriuate Web site.
