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?
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).
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.
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 *
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.
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.
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.
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?
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?
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
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.
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.
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.
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?
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?
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
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.
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.
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):
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?
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.
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.
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)............
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?
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.
