Excel 2010 :: Create Database That Contains Data Taken From Several Workbooks?
Mar 8, 2014
I am trying to create a database that contains data taken from several work books that i have saved in one folder on my hard drive. These workbooks contain quite a lot ** data, but i only want to extract the data i want to instead ** having to extract it all? these works books are used by several other people as well, so i want it to be able to update as data changes.
I was thinking about using data links, but that only seems to extract all the data instead ** just what i want?
View 1 Replies
ADVERTISEMENT
Mar 19, 2014
I'm importing data from Excel 2010 into a database.
I'm joining to 2 cells into one. A1 = 12 Rushwood Street and B1 = London. So C1 = 12 Rushwood Street London
I need C1 to look like this:
12 Rushwood Street
London
I tried wrap text but when I import it comes out as one line in the database.
View 3 Replies
View Related
Jul 9, 2012
I got a master format in xl2010 to collect data, which is being circulated between my team. members fill in their respective data n mail back the file with their name attached to file name. i want to creat a macro which can copy the data from every members file to master file.
View 1 Replies
View Related
Feb 16, 2012
We have a master worksheet, and I cant let staff see it, but I want them to update 'their bits' themselves, rather than email the admin staff and let them update the master sheet..
every job is on its own row and contains a unique ID.
is there a way i can merge their data in to the correct row and column(s) on the master sheet?
i've been playing with datasources etc
Excel 2010
View 2 Replies
View Related
Jan 11, 2014
I have a text file which is attached as "rawdata". It contains records of something (let's call it temperature) at different times on different days. My goal is to display a graph of temperature versus time so that I can visually analyze trends. I have hundreds of these files, all of different lengths. it is very important that I automate this process as much as possible.
Detail: (Here I describe what I have done so far; if this is inefficient or unnecessary, feel free to tell me) I open Excel 2010, click File, Open, and select the file that I want to parse. It is a TXT file, so the Text Import Wizard comes up. For step one, I select Fixed Width. I select File Origin: MS-DOS (PC-8). On step 2 of the wizard, I create column break lines to place all dates in the far left column. The next column contains the first column of numbers before the first dash (-). The next column contains only the dash - I will later select "ignore this column" to eliminate them. The next column contains the time stamps. I continue adding column breaks in the wizard until all of the data are parsed into columns in the same manner.
In step 3, I format the first column as "date (DMY)". The columns with the dashes I select "do not import". Everything else is "general". I click "finish", and the resultant workbook is attached, called "import".
Now, as to what I want to do: I want to display the "temperatures" as a graph vs a date/time axis. The reason I find this difficult is because the temperatures and times are not in neat columns, but are in 4 columns that go in a left-to-right and top-to-bottom progression and are broken up every few lines. (I am interested only in numbers that are displayed immediately to the left of a time-stamp. Therefore, the "record #"s should be ignored. We can delete the rows that say "record #" if can be done automatically.)
View 3 Replies
View Related
Sep 19, 2013
My situation is as follows:
1. I have one sheet with all the information about my customers, such as customer name, address, mobile number and email. I have also added customer reference no...... this could act like a primary key in databases...... This is SHEET 1.
2. my other sheet, lets call it SHEET 2.... is basically a template of my quotation where at the top I need to enter the customer details such as customer name, address, mobile number and email.. Sometimes I have returning customers and I need to enter their details again and again.... it is time consuming.
Initially I would like to enter all my customers detail into SHEET 1.
When I have to create a quotation in SHEET 2, I would like to enter a customer reference number and it should bring all the data from SHEET 1 about that customer and place it into SHEET 2 (quotation template)...
View 3 Replies
View Related
Nov 30, 2011
I'm trying to create a pivot in 2010, which gathers a cumulative amount from data sources within two different worksheets.
View 2 Replies
View Related
Jun 27, 2012
I am trying to create simple leave data base using Excel. Is it easy or complicated?
View 2 Replies
View Related
Jan 20, 2013
I have an assignment for work where I am required to create a button within an excel sheet that will submit any text in a cell which in this case would be "A9" to a database to a certain field. I must create this using visual basic.
View 1 Replies
View Related
Nov 6, 2012
I am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered)
Device (Filtered)
Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
Jan
Feb
Mar
Aprl
May
Start
=formula?
View 4 Replies
View Related
May 14, 2014
I'd like my macro (in an Excel 2010 workbook) to copy values from three worksheet cells and append them to an existing Access or SQL database file -- without having to leave my Excel spreadsheet or open any other programs. The database file simply needs to be appended. Keeping a running list. That file isn't being used for anything else, we can set it up however is necessary to do this.
For example, Excel cells A1, A2, and A3. Representing Job Name, Job Number and Job Total.
how make VBA do this(in SIMPLE terms)? Or give me a link of where to look or what to search for? I understand VBA but not Access or SQL.
View 1 Replies
View Related
Jul 22, 2014
I already transfer my data from database (excel.xlsm) to an old worksheet (excel.xlsx) with a table by using VBA. Now I want to make this old worksheet become a new file with new file name like yyyy/mm/dd/where. Is that possible?
View 4 Replies
View Related
Sep 12, 2006
I've been looking around to find a solution for my problem and as a last resort I have decided to make a post, and I will get straight to the point. I have 3 xl Files of relevance:
1 - is my "database" which consists of multiple sheets with different information in each. Each sheet is correlated with each other by one common ID. 2 - is my "template" which is 2 sheets, which functions as a report. The template serves as the Report which will be printed. The data from each row from the database can fill the spaces in the template.
3 - "the tool" xl file is where i have my macros and the mapping for the which columns from the database belong to which cells in the template.
Basically what I'm trying to get to work is: User opens Tool clicks "create Reports" and the tool should then open the database, pick the range of rows from the database put the data into the relevant cell in the template save the "template" with the info on it, close it and do it again with each row of data from the database. So if I pick rows 4 to 34 in my tool, it should create 30 xls files from the template and fill in the data from each row into each newly generated "report".
View 4 Replies
View Related
Feb 28, 2014
In Excel 2003 multiple Excel workbooks were separate tabs in the Task Bar.
Very easy to tab between open workbooks, with the keyboard -OR- minimum mouse travel to select an alternative tab/workbook.
In Excel 2010 there is only one tab for "Excel", and the only way I can find to toggle between workbooks is the "File" ribbon tab and the oceanic flourishes of "lists" and mouse travel and clicks ... to activate another open workbook. And guessing at file names because the left hand strip, "Recent Files", is way to narrow for even a modest file name.
Is there a setting/shortcut-keys that makes tabbing between workbooks more 21st century "time" efficient ?
View 11 Replies
View Related
Nov 27, 2013
make a list of all Excel Workbooks in specific folder(My Documents). And once clicked, it will open the file immediately.
View 10 Replies
View Related
Jan 9, 2014
I have three workbooks. Every workbook has 50 persons IT information. Those are CPU list, Monitor list, UPS list. I would like to create another workbook. Where all user's IT information will be available. Like X user's information required, under x information his CPU, monitor and UPS information link will be available. How to create link of the CPU, UPS monitor sheets with new workbook?
View 4 Replies
View Related
Jun 20, 2014
On my work computer (Excel 2010, Win 7) when I am stepping through my VBA code using F8 and come to a Workbooks.Open line, the macro automatically kicks in and runs through to the end instead of just stepping to the next line of code. This happens nearly every time but occasionally it works as it should. To prevent this, I have to put a breakpoint on the next line following Workbooks.Open and after that I can step (F8) through the rest of the code. I have never had this problem before and it doesn't happen on my home computer.
View 4 Replies
View Related
May 2, 2012
I am attempting my first working macro. I am currently using Excel 2010 on Windows XP. Here is what I am attempting: I need a very user friendly macro that will allow my staff to click on a button to run the macro. Each month they receive multiple workbooks from one of our clients. The workbooks are always stored in the same location. I need the macro to go in and pick up the first worksheet of each workbook and copy it into a new workbook. Each original sheet1 of each workbook should have its own sheet in the new workbook, i.e. if there are 5 original workbooks then the macro should create one workbook with 5 sheets. Here is the programming I have been using:
Sub GetSheets()
Path = "C:Documents and Settingse462863My Documents
utterblotter"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
This works, but I am running into a few problems. I need to create a "run" button but I'm unsure how to build it. Also, when I run this macro there are tons of extra sheets and the worksheets tend to duplicate. I am wondering if the "loop" is picking up more than I need.
View 9 Replies
View Related
Apr 2, 2014
Im trying to copy multiple workbooks and just save it into only one worksheet. I have 2000 diffrent workbooks with the diffrent amount of rows, The ammount of cells is the same and it dosent change.
im working on a excel 2010
This is what i got for the moment..
Sub LoopThroughDirectory()
Dim MyFile As String[code].....
View 1 Replies
View Related
Mar 28, 2013
I've got 3 columns of formulas that end up doing what I need, but I'm thinking it could probably be done easier with VBA.
I have 1 book with a sheet I'll call Log & another book with a sheet I'll call Source. On the Log, column E has the first 9 characters of vendor names & /vendor number (ex: EDMUND FI/00250), along with other data out through column P. On Source, the vendor names are in C & a short code for their name is in A.
I need to compare the first 9 characters in Log col E with the first 9 characters in Source col C. When a match is found, I need to return the short code from Source col A. BUT - if there's a duplicate in either Source col C or A, instead I need to return the word VERIFY, preferably with the cell highlighted in red.
Right now, I have, on Source col L:
Code:
=MID(C2,1,9)
and in source M:
Code:
=IF(OR(A2=A1,A2=A3,L2=L1,L2=L3),"VERIFY",A2)
This gives me the first 9 characters of the vendor name in L & the short code OR VERIFY in M
Then, in Log col R, I have an array formula:
Code:
=VLOOKUP(MID(E4,1,9),'[PRETICKET P ADDRESSES working.xls]Paddress'!$L:$M,2,FALSE)
This gives me either the short code or VERIFY from Source M. Also, sometimes it doesn't find a match & it returns #N/A. Then I have to do Conditional Formatting to make the VERIFY cells red & I thought I'd make the error cells be yellow (although I haven't figured out the CF for that yet)
Is there a better way to do this with VBA? I'm working with Excel 2010; just got it & still figuring it out.
View 2 Replies
View Related
Aug 20, 2013
The easiest way I can describe the scenario I am trying to create, is to use a company list of personnel (my index) and to generate a time-sheet workbook per person based on a pre-populated template. This time-sheet is saved as the persons name and has the persons name entered into cell D:10
I have a workbook which contains two worksheets;
1) An Index sheet which contains a list of names that I wish to use in Q16 downwards (note the length of this list will vary each time I run this)
2) A "template" sheet which I wish to duplicate in new workbooks
3) A second "data" sheet that I wish to copy across in new workbooks
I need a macro that will take the "template" and "data" sheets and copy it into a new workbook, renaming each new workbook to each name in my Index sheet. I also want that same Name to be copied into cell reference D:10 of the "template" each time.
The end result is that I should have a series of new files generated and saved which are named the same as the Index list, with both the "Template" sheet and the "Data" sheet present, with the cell D:10 pre-populated with the Name provided in the "Template" sheet.
I'm using Excel 2010.
View 9 Replies
View Related
Jan 24, 2014
The situation I have on my hands is over 200 users, were the majority can't use Excel's built in function to create PDF's.
Yes, I've tried to teach them, but I'm probably not a good teacher.
So, I've resorted to create a button at the top of the worksheet, that says "Create PDF". I thought this was gonna be very easy to program, but alas.
Is there a way to start the built-in Excel dialog that happens when you click "Create PDF/XPS" in "Save & Send"?
View 4 Replies
View Related
Dec 26, 2012
how do i make a bar chart on Excel! 2010
Basically i have these figures
Male : 21
Female : 18
I want to make a bar chart, When i use Excel and try to make it - i get a chart which shows the right column height but the Male and Female bars are BOTH IN 1 Colour. How do i make it so that male bar is blue and female is pink.
View 3 Replies
View Related
Jan 6, 2014
I am not too sure what this is called but I am using Excel 2010 and need particular document that I use repeatedly. Basically, if a particular cell in the document does not contain a phrase then I want Excel to automatically change the text in that cell to bold and red and 2 other cells in Excel to bold and red as well. For more information, the cell is 19–20 and K–O and the phrase in the cell is "No Inconsistency".
So if "No Inconsistency" is not found in that cell then it automatically changes the text in that cell to bold and red, and it also changes two other cells that are found in that document to bold and red as well. The coordinates of those two other cells are 19 with H–J, and the other one is 17 with H.
View 2 Replies
View Related
Mar 19, 2013
Im looking to create a bar chart in Excel 2010 where the totalnumber of widgets is 33 and the number produced so far is 11. The bar chart would be a single bar that represents the percentage complete (33%) with the chart maximum range being 100% which would represent the number 38.
Ive included a mock up example of what the data and chart would look like
View 3 Replies
View Related
Feb 7, 2013
I'm trying to create a userform with a progress bar. The progress bar does NOT need to be 'real' and accurate, but simply act as a timer to illustrate to the user that the program is running some quick calcs.. Again, basic progress bar, with about about a 4 second completion time..
(using excel 2010 VB)
View 6 Replies
View Related
Nov 18, 2013
Code:
Date Jan-14..........Jun-14............Dec-14...........Jan-15..........Jun-15............Dec-15
Measure1
Measure2
Measure3
Code:
Date 2014 2015 2016
Measure1
Measure2
Measure3
I have two tables, examples above In the first table, Jan-14 is a dropdown value that updates all the values to the right by a month increment when a specific date is selected
I then have some code that populates the cells which have 2014,2015,2016 in them.
What I need to do next is populate the 2nd table with values based on the date ranges in the above table.
View 4 Replies
View Related
Nov 26, 2013
I'm trying to create a pivot in vba. I can record what I have done but if I run that recorded code, it returns an error "Invalid call procedure or argument".
Code:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=Sheets("Raw Data").Range("A1:AG" & Z), _
TableDestination:=Range("A3"), _
[Code].....
View 1 Replies
View Related
Jan 7, 2014
I have a workbook which needs a new sheet for each day of the month.I normally just create and re-name each sheet with the date...is there a simpler way where i can create the new sheet with a date for each month instead of doing indiviually?
Excel 2010
View 9 Replies
View Related
Feb 12, 2014
I have 6 columns. The 5th and 6th columns are the most important. The 5th contains a figure and the 6th contains a type.
Bascially I am trying to do a basic chart showing the following 2 things. A. Do a percentage chart of column 6 (service) but not of percentage of hoem many lines the product is in. Not based on percentage of how many times Apples are shown compared to other products. I need it to add up everything, then do a percentage of total amounts of oranges and total of for Apples from the figures on column 5. Also, if possible, the pie chart will even show teh actual total figure per product in each (or next to) each pie chart slice.
know the easiest way to do this (step by step) in Excel 2010 and then I can perhaps write a macro for the future?
View 1 Replies
View Related
