Collating Data From Multiple Spreadsheets In A Specific Folder
Apr 13, 2007
I have built a timsheet template which I intend to issue to c.140 personnel in my company to fill in for the month of May. On receipt of these timesheets, I will save them all in one, specified folder. I would then like to consolidate the total number of hours worked by adding cell N53 from each timesheet received. Is there any macro/ VB script which can add the contents of a particular cell (N53 in this case) from all the spreadsheets contained within a particular folder?
View 2 Replies
ADVERTISEMENT
Apr 27, 2014
The link to my file is: [URL] ........
I am using excel 2003.
My query goes like this:
I need to find out "total lifted quantity"(i.e. the sum of 'first state lifted quantity'+'second state lifted quantity'+'third state lifted quantity') for each "state" for each "size" and each "grade".
However when I am applying a pivot table (as seen in Sheet1) it is not giving data in lucid form. I don't want to split the data for three states in three different tables (as seen in Sheet 4).
View 1 Replies
View Related
May 2, 2014
I have two Spreadsheets on Excel 2003, one provided by a service supplier (i), and one from my own system (ii).
I need a macro attached to spreadsheet ii that looks for data matches between the F column of my spreadsheet, and the C column of the suppliers. Where data matches (i.e. if cell C42 in spreadsheet ii is the same as cell F23 in spreadsheet i) it transfers the contents of the A column of spreadhseet i and places it in the corresponding cell in the C column of spreadsheet ii.
Example 1 : In spreadsheet i cell C20=BN213YD. In spreadhseet ii cell F55=BN213YD. Therefore, the macro takes the contents of cell A20 in spreadsheet i and places it in cell C55 of spreadheet ii
Example 2 : In spreadsheet i Cell C75 = TN142XS. In spreadsheet ii this data is not found. The macro therefore takes no further action.
Basically, the suppliers spreadsheet has less information per row, but more entries overall. My spreadsheet is lacking a lot of suppliers references, but has more comprehensive information elsewhere. Until now i have been doing a manual search of the postcodes between the spreadsheets and rectifying them manually, but this is taking longer and longer as the spreadsheets grow.
View 1 Replies
View Related
Aug 12, 2008
I have attached an excel spreadsheet. I am trying to automatically calculate the data in the boxes, (Without using a Pivot Table) but have tried a variety of ways with COUNTIF, COUNT(IF()) and SUMPRODUCT, with confusing results. I managed to get a query to work with SUMPRODUCT, however it does not accept Wildcards (*) in the text reference. I has spent over 8 hours, and even tinkered with Pivot Tables, all without success - The data I would like to extract in the columns for summary is: Department * Logs <7 Days old *Average Log Age * Logs >7 Days Old * Average Log Age *
View 6 Replies
View Related
Jun 20, 2014
I am creating a MIS for my team. But I am now struck while creating dashboard. I have a workbook with 12 raw data spreadsheets for specific months. Now what I want is that in 13th sheet if I select January then 1st cell in 13 sheet will show the value January cell 1 value. However if I select February then 1st cell in 13th sheet will show the value of February cell 1 value.
View 7 Replies
View Related
Apr 16, 2014
I have several excel spreadsheets by which I would like to add together certain values in the column with the same columns of all the other excel spreadsheets. So basically, If one of the values are "SELF" , I would like to calculate how many times that value occurs in all the spreadsheets. Any easier way to organize the data to be able to do this also.
View 1 Replies
View Related
May 23, 2012
I have come across a problem which delivers 0 as a result, I am doing sumifs which i never had a problem before with, however I am trying to include a cell which is variable by using the & in collating data, the raw file also contains that criteria in 1 column so I have no mismatch.
View 5 Replies
View Related
May 7, 2012
I am using some code to loop through a folder and extract some data from a specific sheet.
Now say a user moves the file to another folder etc... The macro will pull up an error, now is there a way i can have it dynamic linked to the spreadsheet so no matter what folder it gets moved to,
My folder path will adjust to that or need to take another route?
View 2 Replies
View Related
Nov 21, 2012
I have to open each pdf file in a specific folder, select and copy all contents and paste it in column 'A' of a new workbook.
I have tried following code but not worked...
Code:
Sub ConvertPDF(control As IRibbonControl)
Dim AdobeApp As String
Dim AdobeFile As String
Dim StartAdobe
Dim fso As New FileSystemObject
[Code]...
I have Acrobat Reader installed. Any method without using 'SendKeys'?
View 1 Replies
View Related
Jun 13, 2014
I am trying to figure out if there is a way to add up all the results of all spreadsheets within a single folder, either from a local drive or from a network drive?
View 1 Replies
View Related
Sep 13, 2013
I'm trying to create some vba code that will go into other .xlsx documents, pull all cells with a value in the first 30 columns and then return them as a table. Basically each work crew has a .xlsx spreadsheet containing their schedule and I am trying to bring them all together into one nice little package. If I go to import from external sources then click XML data, navigate to the folder they are in and them click show all files and pick my spreadsheet it imports nicely. Then I go to the next blank cell in column "A" and repeat for the 4 crews. Now I can filter by each crew but all 4 are visible on a single page as a table. I recorded a macro that shows everything I did, but it is not dynamic (the .xlsx source files name changes based on the month) I'm running XP sp3 with Excel 2010
Here is a copy of what I have
Code:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:Documents and SettingsUSERNAMEDesktopSCHEDULE STUF" _
, _
"FAcrewCurrent.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _
, _
";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;J" _
, _
"et OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt " _
[code].....
Is there a better or simpler way to do this? The tables don't need to be forever linked to their previous spreadsheets so if copy/pasting is a better way than I am all for that as well.
View 1 Replies
View Related
Jan 13, 2014
How to perform this function on Google Spreadsheets (which appears to be slightly different to Excel);
Calculate the sum of the numbers in column D that occur on the 10th January 2014?
And also, Calculate the sum of the numbers in column D that occur between 9am and 10am on the 10th January 2014.
View 1 Replies
View Related
Oct 17, 2008
I have a workbook that has multiple tabs and need help trying to figure out how to consolidate all the data. I find myself spending hours doing this manually each day.
Here is what I have:
Workbook has tabs labeled....Wk1_Mon, Wk1_Tues, Wk1_Wed, Wk1_Thurs, Wk1_Friday, Wk1_Summary......and repeats all the tabs through Wk5....then I have a Month_Summary tab.
I have 25 users with 25 seperate workbooks each with individual information on each workbook.
I am trying to get a sum of all the data on the Month_Summary tab for each month for each user and as well as a sum of the Month_Summary tab for all 25 users.
The end result I am looking for is to get a Yearly Sum of all the Month_Summary Tabs for all 25 users as well as individual yearly summaries for each users.
I have one main Folder which contains 25 folders (one for each user). Under each user folder there is a seperate Workbook for each month.
View 2 Replies
View Related
Dec 3, 2008
If you have a spreadsheet where column A is a list of names, for each name there are several data types (listed in column B) some of the data types are repeated. Column C contains the percentage for each of the data tpes for the whole thing. There are 300+ names, each with several data types and most where at least one data type is repeated. How can i easily calculate the total percentage for each data type, for each name.
View 10 Replies
View Related
Jun 13, 2014
I have 3 columns sports played then a winners column then a runner up column.
what formula can I use to search through the winner and runner up columns for a particula team say Hinuera (would do it for every team involved in the competition) and then get 2 lists produced of what sports they won and what they were runners up in?
View 7 Replies
View Related
Dec 11, 2013
I have a spreadsheet with around 250 tabs, all with a small amount of information on laid out int he same format. My question is can i collate this into one long tab?
View 5 Replies
View Related
Apr 19, 2012
How can I get just the inner Folder (red) in this path? Example given.
C:Working_DirectoryPersonalFolder
View 2 Replies
View Related
Dec 9, 2013
i want if i insert picture name in a cell it should get picture from a specific folder.
View 3 Replies
View Related
Aug 11, 2009
I have copied the below coding from the forum.
View 3 Replies
View Related
Jul 30, 2014
VB code, recorded by macro, it is working, but I need create different workbooks with different names , this one create just one specific name here is the first problem , second would be for saving in desktop folder "Ataskaitos" here another because it just for my computer , on other computer directory would not be found and third , then I deleting existing sheet in workbook from there I run the macro and copy sheet to new workbook it asking if I sure want to delete that sheet, so I don't need asking that I just need to delete it
VB:
Sheets("Ruooinys" & i).Select
ActiveSheet.Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Cells.Select
[Code] .....
View 2 Replies
View Related
Apr 29, 2014
Looking for VBA code to automatically save an excel file as an .xlsx format, and saving to a specific folder: C:UserswolfmDesktopLoad Lists
I have been using the attached code, but it's not working. It saves to the desktop, not the folder. Also, when it creates the file name based on sFile = ActiveSheet.Range("C4").Value & ".xlsx", it adds wierd things to the file name. I want the file name to be simply the value in C4.
View 2 Replies
View Related
Nov 12, 2008
I am trying to generate a folder within a specific location (For now lets call it C:Jobs) that will be named as per the adjacent cell (Column H on attached)
Obviously if the folder already exists I want the code to stop.
But this code will have to generate a different folder for each row within the spreadsheet.
View 8 Replies
View Related
Jun 16, 2009
I have found out how I save my document as workbook name and date, but now I want to add the saved file to a specific folder instead of just in my dokument folder. This is what I have done so far (SAVING FILE IN A SPECIFIC FOLDER):
View 5 Replies
View Related
Jul 10, 2006
I have found out how to creat a new folder, and now what to copy specific files over to this file from a different Directory. Does anyone know how to do this?
&
How to place todays Date at the end of this New Folder?
CreateDirectory ("Y:CostWallyPast Daily RunsTest")
Like for it to Read 'Test 07-10-06'
View 3 Replies
View Related
Oct 27, 2006
I am trying to have a worksheet linked to another workbook, but this workbook name will change. It will always start with Blockbuster - Valuation but the rest of the file name is the date and the version of the workbook. Do you know how VBA can go in a folder and look for the entire file name of a workbook starting with Blockbuster - Valuation.
View 3 Replies
View Related
Oct 23, 2007
I'm having a problem with how excel opens my files. Up until yesterday when I would open any of my spreadsheets they would open seperately. I would have multiple files open across my taskbar and could easily switch between files with a simple Alt-tab windows function.
Now when I open multiple excel files it opens them under "one" file so to speak. The only way to switch between files is if I go to Window>then select the file I want or minimize within excel so I can see the other spreadsheets.
View 2 Replies
View Related
Sep 28, 2009
I am trying to find a vlookup formulae for multiple spreadsheets in excel.
I have the below data that i need to lookup, test and produce an output.
Main Spreadsheet:
Column B...........................Column C
A 0000564235.................... <Desc> <---- Desired output is Coumn C from searching all part numbers in each spreadsheet.
Spreadsheet 1:
Column B (Part No.) ..........Column C (Desc)
A 0000564235................................Hose
Spreadsheet 2:
Column B (Part No.) .......Column C (Desc)
A 02315645646 ............................Clamp
Spreadsheet 3:
Column B (Part No.) .......Column C (Desc)............
View 11 Replies
View Related
May 7, 2014
I was curious if there was a way to combine multiple spreadsheets that have multiple tabs all onto 1 spreadsheet?
View 9 Replies
View Related
Mar 20, 2014
I have a workbook that I add about 20-35 tabs in a month. To individually go through and protect the sheet will take too much time, is there another way where I can select multiple tabs so the worksheets can be protected?
View 8 Replies
View Related
Apr 28, 2008
i have admit forms for multiple patients, about 200 or so already done, and its set up to make an upload sheet, which we then add all of them to one big file to import to access. i am in the process of cleaning things up, but we need to add a formula to a cell to determine length of stay, and several similar things, but i'd like to not have to go and do it file by file.
is there any way to update all the files at once? some kind of automation? then changes would be in the same cell for each file.
or maybe some way to do a batch modify or something, so it can make the change, and run the two macros, one to make upload for file, one to dump it in the file that goes into access.
View 9 Replies
View Related
