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 RepliesI'm trying to extract data from an external spreadsheet, while leaving that spreadsheet closed... I do not know VBA at all, and I'm unsure where exactly to insert code. See my attached file for details, as I wanted to keep this post short, to comply with your rules
View 7 Replies View RelatedI 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 RelatedI have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
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 View RelatedI'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.
I'm working on a sheet trying to keep track on which client is working with which employee. The first sheet shows tracking for the year and each additional sheet is the month. Basically the Yearly sheet is there so I can quickly control+f "client name" and see which employee he or she is working with.
I attached a simple version of the workbook but basically it basically looks like this: Yearly sheet - Row 1 is the employees name, Row 2 is the month, and the rows after that are the clients name until it gets to the next month. January sheet - cell A1 says client, Cell B1 says employee. I'm inputing the clients in column A and the employee in column B and using this formula for the Yearly sheet (starting in row 3) =IF(January!$B2=Yearly!A$1,January!$A2,"")
This is working for what it's supposed to do, but it's not pretty and leaves a lot of of blank cells. Is there a simpler way of doing this?
YearlyClientsEmployees.xlsx
I have the job of producing a survey in Excel and collating the results anonymously, to help with this I'm looking for a chunk of code that will enable me to pull a specified range of data from all the files in a particular drive with a filename starting with a specified word and insert them all in to the same blank sheet.
View 6 Replies View RelatedI had a friend attempt to help me with this issue but he couldn't figure it out. He suggested that I tried asking around here.
Here is what I am trying to accomplish:
(I am happy to email anybody the spreadsheet to help out with this description)
1. There are two spreadsheets. One spreadsheet with about 25 tabs (one for each store number) and one spreadsheet with one tab and about 1400 lines of information that is sorted by store number. To preface, this 1400 line spreadsheet can be 900 lines one month and 2500 another month, so it fluctuates.
2. From this spreadsheet sorted by store, I will highlight, cut and paste into the corresponding store tab on the first spreadsheet. Also, at any given month, the information fluctuates, it can be 15 lines for one store one month and another month it could be 5.
3. Once I am done cutting and pasting it all into each seperate tab, I have a 26th tab that I am looking to create a button that will then pull all that information into the 26th tab.
I know - first question is why would you want that? You already have all the information from that original spreadsheet. Well, in these store tabs, I have everything perfectly formatted a certain way and I only really cut and paste the information that I need from the 2nd spreadsheet.
I have a spreadsheet that my friend claims he got close to working it out.
Just to add, I would need this button to take into account that the information in each tab fluctuates and also that it is easy to add a tab if I add stores.
I am trying to Loop Through a Folder Of Excel Workbooks and get required Info from one of the workbooks, back to my Master File.
I thought of "VLOOKUP" but I am not shore if it will work over Approx. 60 workbooks? if there is an other or better approach, I am open to all Ideas.
So far I have code to Open my Master and declare the criteria for the VLOOKUP with the help from:
Re: Run A Macro On All Files In A Directory
--------------------------------------------------------------------------------
See also this page on how to loop through a folder
Loop Through Folder Of Excel Workbooks
_________________
I am now able to open all Files in the sub directory, now I need to know how to approach my problem of looking for and getting the Info back to my Master File with approx. 5000 entrys to look for?
I have a spreadsheet with 12 tabs (one for each month of the year). What I need is a macro/function that on execution will pull all rows from each sheet that has the word "overdue" in cell E from E9 down. I need the whole row of data being taken into a new sheet.
So for example, in each sheet there could be the word overdue appearing in 30 out of 500 rows I need those complete rows (A to Y) being put into another sheet for ease. At the moment I am filtering each sheet and copying and pasting into a new sheet for each bloody sheet (LOOOONG way).
The worksheets are titled: Jan 14, Feb 14, March 14, April 14, June 14, July 14, Aug 14, Sept 14, Oct 14, Nov 14, Dec 14
So I am trying to pull data from multiple sheets. I've gone through the thread, but haven't found an answer yet (or didn't work hard enough). On Sheet1, yellow highlighted column, I am trying to look up the I.D. Code for 36 month residuals. As you can see, I have to use multiple conditions on different worksheets.
I have to use most of Sheet1 columns to find the answer. I just can't figure the formula out.
I don't have Excel 2007, only 2003 I have.
I'm trying to compile a spread sheet that will pull the play by play logs from the NHL website.
The logs are listed in order.
Game 1 is [URL]... Game 2 is [URL]... etc etc
I am trying to put them all on one spreadsheet. (or to do it in batches of 100, or 50 or whatever the size of the file allows)
As part of report consolidation I need a Macro that can read through all the files in a particular location(Say a folder path) Pull the data for each unique products into separate sheet
In the example below : All the rows with "Chocolates to sheet 1" , Drink to Sheet 2 and so on Since the products in column 3 can vary the unique sheets need to be created at run time based on the source data
Workbook1
WEST
100
Chocolates
[Code]....
Data extract from sheet to sheet through column header values, i have set of data on sheet1,2..loop end of sheet
i want pull column data from multiple sheet column values into masterdata sheet depends upon column header values assigned in masterdata sheet.. my code works fine! but i want pull data if column header repeats more than one also
SO_NBR this is one heading in multiple sheets i cant pull col values not more than once under if SO_NBR repeats more than once.
[Code] .....
Find the attachment mentioned as expected result sheets is my requirement : HEADER_UPDATES_AIO_$.xls
I need a macro which collects data from two files and dumps it into a separate spreadsheet. I need it to prompt the user to select the two files, since the file names may change.
Once a user selects the files, the macro simply opens them, grabs the data (the data sits on a single sheet in each file) and pastes it into a file, and closes the 2 data files.
The Data sits in A7:N20 on both files.
Attached is a master metrics deck, where data needs to be refreshed every week, from two different workbooks.
The catch is, the two parent workbooks would be new versions every week (data would be same format, and style within), but file name would change each week, for eg: for 1st workbook, 'XXX_Weekly_Week24.xlsx' to 'XXX_Weekly_Week25.xlsx' and so on.
for 2nd workbook, 'YYY_Weekly_Week24.xlsx' to 'YYY_Weekly_Week25.xlsx' and so on.
In the master metrics deck, a consolidated vew of some of data from workbook 1 and workbook 2 is presented for each week.
This master file would remain same, and only be refreshed each week (preferably with a button on its sheet "REFRESH!!")
Next, need to manually change cells C2:G2 each week to reflect week titles correctly.
For e.g.: week6 would show week 1 through week 5, but during week7, it needs to show week 2 to week 6. Only last five weeks of data any given time.
The first datarows of each set are conditionally formatted (they are compared with other rows in the set and need to reflect danger values, good values etc) (I have done that already).
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.
I need a Macro which pulls the data from different sheets of excel (which is not formatted properly) to Main Sheet. Also some of the columns will not have the same names, so macro should handle this exception as well.
View 8 Replies View RelatedI'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 was curious if there was a way to combine multiple spreadsheets that have multiple tabs all onto 1 spreadsheet?
View 9 Replies View RelatedI 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 Relatedi 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.
after finishing a huge macro, the last step is to take row 1 of sheet 1 and insert it at the top of every worksheet.
This is the first way I tried it. It does absolutely nothing (except copy the row).
For Each ws In ThisWorkbook.Worksheets
Sheets("Sheet1").Range("1:1").Copy
ws.Range("1:1").Insert Shift:=xlDown
Next ws
This was another try, which resulted in a 1004 "Select method failed" error
I have a workbook to collate ratings for staff, there are three sheets, one is a summary of data, and two are sheets that can have data entered into them, called "Eligible" and "Ineligible".
I would like to write a macro to search cells in "Eligible" that contain a staff number, and copy this data to "Summary". If excel encounters a blank cell, i.e it has no staff number in it, it will move to she sheet "Ineligible" and perform the same operation, pasting the data below that which has been copied from "Eligible"
To further complicate things, every time the macro runs to populate "Summary" I would like it to clear the existing data in "Summary", to allow for deletions in "Eligible" or "Ineligible".
I'm trying to set up conditional formatting to show when an employee is both scheduled to be working for the current date, and is trained in a particular task.
I have a spreadsheet called "Training" for all of the tasks that employees can be trained in, with the employee names in each row, and the task names at the top of each column. The intersection of an employee's row with a task's column will have an "X" if the employee is trained in the task. This is the spreadsheet that I'd like to be formatted; if the employee is trained and also scheduled to be working, I'd like to the "X" to change to a different color.
There are separate spreadsheets within the same workbook for the schedule. This is a bit more complex since the since each week of the schedule has its own spreadsheet within the workbook; each spreadsheet is named for the Saturday of the week it is for, such as "06-07". I've been able to successfully have other formulas in the workbook auto-adjust to the current week's schedule by using variations on the TODAY function (something like INDIRECT("'"&(TEXT(TODAY()-MOD(WEEKDAY(TODAY(),1),7),"mm-dd"))), but I'm not sure how to incorporate this into conditional formatting. On each schedule sheet, there is a column for each day of the week and a row for each employee. The cell for a given day will contain the employee's schedule if they are scheduled, or it will be blank if they are not scheduled. So, the conditional formatting formula would just need to check to see if the appropriate cell was blank or not.
How can i combine multiple Excel Spreadsheets into one Spreadsheet ?
View 3 Replies View RelatedI have a master spreadsheet that links to various other spreadsheet for budget figures. I have now created and updated the budgets for the 2008/09 budget year.
It is painstaking to update the links and browse to the new files one at a time and point them to their new folder. Is there a way I can update all of the link workbooks to the new directory in one fell swoop?
I need to combine these 6 spreadsheets into one single spreadsheet. It won't seem to allow me to paste it.
It needs to begin with 2012 and count down.
2011.xls2010.xls2009.xls2008.xls2007.xls