Search And Compile Multiple Spreadsheets

Jul 6, 2013

The company deals with Machine services, we maintain, service and brake-fix machine problems, the machines are manufactured my ourselves and there are approximately 15 different machines.

We have a Field Service spreadsheet that is filled in by all service engineers for every job they go on, this is filled in with information like:-

Serial Number
Machine Type
Hours Run Time
Reason for Visit
Reported Problem
etc, etc

All the forms are identical so all the fields are identical

Field Service Record Spares 2013 Master Rev4 (2).xlsx (this is the correct file). The forms are then saved on a central server folder and stored in month order.

Is there a way of creating a master excel sheet to gather all the information from all the Field Service spreadsheets which would then allow me to search all sheets by serial number or fault, without me having to copy and paste all the information into the master sheet??

We have 10 service engineers and each fills out 1 sheet per job per week, so a 4 week job will have 4 sheets for 1 engineer. an engineer could go on 5 jobs in 1 week and would therefore fill out 5 sheets. so we could be looking at in excess of 500 Field Service spreadsheet per year.

View 6 Replies


ADVERTISEMENT

Search For String Across Multiple Spreadsheets?

Aug 1, 2013

I have 5 sheets in my workbook and I need to find out if a string exists in any of them. The string would always be in column L of the work sheet. I have searched many places but cant seem to get anything to work, is this possible?

View 8 Replies View Related

Search And Compile Answers

Oct 25, 2007

I have a workbook filled with many tabs / sheets. Each sheet contains the same structure of information and isn't an important aspect of this question.

I've looked at this topic http://www.mrexcel.com/board2/viewtopic.php?t=16683 and I'm unable to follow it well.

Is there a way to search all the tabs for a word and if it matches to copy the entire row into the search tab / sheet? Specifically for each and every searched match.

It would save my users trying to search each tab individually and possibly miss the data they are actually looking for. Hundreds of people use this reference workbook which stores where all our documentation is stored across numerous servers. Each row is one document and its corresponding storage information.

View 9 Replies View Related

Search And Compare Cell Values In Different Spreadsheets

Feb 22, 2007

I have two worksheets with products that I need to compare.

Each product has a code and a product name in the following format:
Column A has the product code and column D the product name,for
example:

ColumnA:
AM1BL15X

ColumnD:
AGLO MEL BLANCO 1C 15MM 215X244

Both sheets *should* have the same data in them but there are 4000
products that need to have their description verified.So I need to go
down each row on Sheet1 , extract the product code from
Sheet1.ColumnA and the product name from Sheet1.ColumnD. Then do a
search on Sheet2 for the product code (Sheet2.ColumnA) and verify that
the product description on sheet2 (Sheet2.ColumnD) is the same as in
Sheet1.ColumnD. If it matches,everything is OK. If not there is
something wrong. So Im thinking that maybe the product codes that
dont have matching descriptions could be entered in to a separate
worksheet so that someone can check it later.

Ive been reading around and found these code samples from this site
[url]:


Dim rng1 as Range, i as Long
Dim cell as Range
With worksheet("Sheet1")
set rng1 = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End With

i = 0
for each cell in rng1

if cell.Value worksheets("Sheet2") .Range("A1").Offset(i,0).Value
Then
' do what - they don't match
else
' do what - they match
End if
i = i + 1
Next

Ive been trying to make some changes but I dont quite know how to get this working to do what I want and I only get errors.

View 9 Replies View Related

Search And Compare Cell Values In 2 Different Spreadsheets

Feb 22, 2007

I have two worksheets with products that I need to compare. Each product has a code and a product name in the following format: Column A has the product code and column D the product name,for
example:

ColumnA :
AM1BL15X

ColumnD:
AGLO MEL BLANCO 1C 15MM 215X244

Both sheets *should* have the same data in them but there are 4000 products that need to have their description verified.So I need to go down each row on Sheet1 , extract the product code from Sheet1.ColumnA and the product name from Sheet1.ColumnD. Then do a search on Sheet2 for the product code (Sheet2.ColumnA) and verify that the product description on sheet2 (Sheet2.ColumnD) is the same as in Sheet1.ColumnD. If it matches,everything is OK. If not there is something wrong. So Im thinking that maybe the product codes that dont have matching descriptions could be entered in to a separate worksheet so that someone can check it later.

Ive been reading around and found these code samples from this site ...

View 2 Replies View Related

Compile Entries From Multiple Workbooks

Feb 6, 2008

I need to compile entries from several workbooks (one for each day of the month), from Column B every time, into a master workbook which will display all of the data from the columns B, laid out in individual columns in the master workbook. i.e. if Workbook1 contains the numbers 1, 2, 3 in cells B2, B3 and B4, and Workbook2 contains the numbers 4, 5, 6 in cells B2, B3 and B4, then I want the master workbook to run a macro which will put Workbook1's numbers into cells B2, B3, B4 and Workbook2's numbers into cells C2, C3, C4and so on for each day of the month. The idea is to be able to compile the month's data so as to plot charts per row.

Each month has it's own folder and each day has it's own workbook. File names are in the following format: YYYYMMDD.xls
I've tried several times, including the use of loop code found here: http://www.ozgrid.com/VBA/loop-through.htm but to no avail.

View 9 Replies View Related

Compile List From Multiple Columns / Rows

Jan 17, 2014

I want to essentially create a meal plan and then have my shopping list generated from the ingredients columns/areas into a separate sheet for printing. I need shopping list generation. I have attached a sample of what I am currently working with but still needs a lot of tweaking. I am not the most advanced excel user as you can see but I continually try to improve upon all of my work.

Test_Meal_Plan.xlsx

View 2 Replies View Related

Compile Data From Multiple Users Based On Criteria

Aug 18, 2011

I have a few people all entering in data into excel, since they cannot be all in one sheet they each have their own copy. I would like to be able to extract all data where column P reads "Red" from each users spreadsheet and then delete the corresponding lines from their spreadsheet (end of day activity)

The Sheets will be named
FDEntryU1AM.xlsm
FDEntryU2AM.xlsm
FDEntryU3AM.xlsm
FDEntryU4AM.xlsm

[Code] .......

I will then need to do the same in another workbook for all items Labeled Blue, but that of course will be same code with just an item replaced.

The idea is after they close up the person overseeing the data can import all at once and leave the users with fresh empty workbooks.

View 9 Replies View Related

Compile List Of Specific Data From Multiple Columns

Aug 30, 2006

I've been trying to create a formula that will take data with a specific status and put those in a list on a summary worksheet.

On my data worksheet I have two columns a category/ name column and a risk/status column, on my summary page I want to generate a list solely made up of names that have a specific status.

I've attached a sample.

View 4 Replies View Related

Switching Between Multiple Spreadsheets

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

VLookup Multiple Spreadsheets

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

Combine Multiple Spreadsheets?

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

Protect Multiple Spreadsheets?

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

Modify Multiple Spreadsheets At Once

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

Inserting To Multiple Spreadsheets

Jun 20, 2008

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

View 9 Replies View Related

Copy From Multiple Spreadsheets

Dec 1, 2006

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".

View 9 Replies View Related

Compile Error - Variable Not Defined Involving Multiple If Statements

Jan 27, 2014

I am trying to put two subs into a macro. They work individually, but when I put them together, I get a "Compile Error: Variable Not Defined" Message. I was told I have to define the variable in the sub multipleif(), but I am not certain as to how to do this. The error message highlights the line I have bolded below.

VB:
Option Explicit
Private Sub CommandButton1_Click()
Dim mth As Variant, txt As Variant, des As Variant, wdt As Variant, I, Cell

[Code]....

View 5 Replies View Related

Conditional Formatting Across Multiple Spreadsheets?

Jun 5, 2014

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.

View 4 Replies View Related

Transfer Data Between Multiple Spreadsheets?

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

Using Multiple Spreadsheets To Pull Data Onto One

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

Combine Multiple Excel Spreadsheets

May 19, 2014

How can i combine multiple Excel Spreadsheets into one Spreadsheet ?

View 3 Replies View Related

Updated Links To Multiple Spreadsheets

Oct 16, 2008

I 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?

View 6 Replies View Related

Combing Multiple Spreadsheets Into Single One

Dec 18, 2012

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

View 1 Replies View Related

Cross Referencing Multiple Spreadsheets

Mar 6, 2013

I have a workbook with two spreadsheets. One contains customer information. The other line items and shipping information. I would like to pull the customer number over to the line items spreadsheet. Both spreadsheets have the order number, but note there is a new line for every item in an order on the line items sheet. How do I accomplish this?

View 2 Replies View Related

Multiple Spreadsheets Share The Same Macro

Feb 3, 2010

I currently have a macro that can be used by multiple spreadsheets. Is there that I can execute the macro within any specific spreadsheet without saving it in each spreadsheet. I just want to place it in a general spot so that in case there is modifications, I only have to change it in one place.

View 14 Replies View Related

Copy Multiple Spreadsheets Into 1 Spreadsheet

Oct 1, 2012

I have created with most of it being done by Austrada (who has done a great job) code to copy multiple spreadsheets into 1 spreadsheet. We have run into a error Run Time Error '9'. Subscript out of range.

Sub CopyData()

'----------------------------------INFORMATION----------------------------------------
'You need to make sure you have activated the Scripting Runtime reference for the FSO to work
'--------------------------------------------------------------------------------------
Dim fso As New Scripting.FileSystemObject

[Code].....

View 4 Replies View Related

For Next Looping - Copying Multiple Spreadsheets

Sep 25, 2006

I am attempting to amalgamate several (100+) spreadsheets into a single spreadsheet. The data is very simple (only three columns, several rows). I have written a simple macro to copy 5 spreadsheets (called TIMESHEET1, TIMESHEET2 etc) into 1 spreadsheet called AMALGAMATED TIMESHEETS. I would like to expand this using a FOR - NEXT loop if possible - or any other way that someone can think of! I cannot use the variable TIMESHEETx as this is not accepted......

View 9 Replies View Related

Importing Macros Into Multiple Spreadsheets

Apr 19, 2007

Is there a way to automate the importing of macros into a list of files ? I have a number of files that I want to add some code to and was wondering if this could be automated as opposed to adding the code to each file manually as it's going to be a regular job.

I've written a routine to loop through the list of files and open them, I just want to know how, if possible, i can automatically import the required macros.

View 6 Replies View Related

How To Create Search Macro Button That Allows To Search In Multiple Worksheets In One Workbook

Oct 11, 2011

Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do

Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then

[Code]....

View 1 Replies View Related

Search Multiple Worksheets Against List Of Non-Exact Search Criteria?

Nov 2, 2009

I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:

1. The rows containing the matched search criteria in the first sheet are highlighted.

2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.

I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved