Program Or Function To Lookup And Locate Typed In Data Across Multiple Workbooks

Jun 12, 2014

have a formula or something along those lines that will look for the data you type in to the selected cell and will show you on how many workbooks it has been entered in. For example, I have workbook a, b, c, d, e, f. all have 12 sheets. On these 12 sheets there is a place to enter the serial number of an item. What I would like to be able to do is on a different workbook be able to type in a serial number and have it show which workbooks it has ever been typed in and its location on said workbooks. and if possible the results of the item which would be whether it was accepted or rejected( this bit is not totally must have. but the workbook and sheet location is a must.)

View 1 Replies


LOOKUP / MATCH Function To Lookup The Owner Name Typed In Cell

Jan 2, 2009

I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.

For example, the data source contains different pets, their names, ages and their owners.

The other sheets are on a one-per-owner basis.

What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:

John Smith (in cell A1)

Pet - Name - Age
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4

View 7 Replies View Related

Lookup Data From Multiple Worksheet Using Indirect Address And Match Function

Sep 28, 2011

determining the correct formula to lookup various details from multiple worksheet:

Data as follows:
Sheet1: input

Sheet2: Data


b2,b3 Formula = Indirect(Address(match(a2,????,0),1,1,1,"Data"))

i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3

and each table has unsorted details compared to others sheets (Data vs Data 2)

View 9 Replies View Related

Lookup Function - Check Batches Of Data / Return Value Dependent Of Multiple Ifs

Apr 3, 2012

Use look up to check batches of data a return a value dependent of multiple ifs?

I have this formula from here


What I am trying to achieve is using the above can it check

C4:C7 Grouped and named "first"
C8:C10 Grouped and named "second"
C11:C13 Grouped and named "third"

If one from each group is selected it returns 1 value, if 2 of each are selected it returns a second value and if 3 of each are selected it returns a third value?

I thought by grouping them it may give me the desired results but all it does is change if I select more than one from any of the groups.

View 2 Replies View Related

LOOKUP Across Multiple Workbooks

Aug 2, 2006

I am trying to create a spreadsheet that would be used as a inventory location log for our raw materials Everytime a raw material comes in our door, we assign a lot number to it (currently a manual system).

I am struggling with how to show the balance on hand in the 4th column of this spreadsheet.

I want this column to look up the balance on hand for that lot # based on the material code. We have up to 100 "material codes" all with different paths to get to them.

For example:

'T:Material CardsCold Roll[(material code).xls]
'T:Material CardsHot Roll[(material code).xls]
'T:Material CardsWire Roll[(material code).xls]

View 9 Replies View Related

Multiple Criteria Lookup Between Workbooks

Aug 10, 2007

My workbook is tracking how many products have been sold and to whom.

Customer Market Apples Oranges etc (10 products) POSTING
xyz South 100
xxx South East 600

I have a second workbook that works out how much it costs us to send the goods and this is a matrix based on where our customer is and which produce they have purchased.

South Apples Oranges Pear etc
South East $x $y $z
North etc
North West

I would like to write a formula under the POST column that takes into account:
The Market. The Produce type (the column heading) based which cell contains a value
i.e using above Row 3 criteria would be Market = South and Produce = Apples. These two pieces of information are then to be used to go to the workbook containing our postage costs and picks up the relevant cost for Apples in the South region.

View 4 Replies View Related

Find/Locate Files/Workbooks Containing Macros

Oct 4, 2006

Is there a way of finding all files in a directory which contain a macro? I have script which will loop through all files in a folder but I don't know, and can't find, the appropriate syntax for referencing macros/modules.

View 9 Replies View Related

Using A Function In Multiple Workbooks

Nov 29, 2009

I have made a function that I seem to use in a lot of my workbooks. I just paste it into which ever workbook I use it. I was wondering if I could have the function in one location and use for all?

View 9 Replies View Related

Lookup Data Between 2 Workbooks

Sep 2, 2006

Spreadsheet 1 is an Excel Spreadsheet. Spreadsheet 2 is a CSV file downloaded daily from UPS. Spreadsheet 2 contains the UPS tracking numbers in a column called Tracking Number. Both Spreadsheets contain a Ship-to- name column. Is there a way to automatically take the UPS tracking numbers from Spreadsheet 2 and place them in a column called Tracking Number in Spreadshhet 1 by matching the Ship-to-name columns between the two spreadsheets?

View 2 Replies View Related

Locate All #N/A Lookup Errors

Sep 24, 2007

I have a large worksheet (about 9,000 rows) with lots of vlookups to another sheet.

It is possible that some of the lookups will return '#N/A' but it is laborious to go through all the rows to check.

Is there a way to have a macro to look through that part of the worksheet, and to pop up a message box with the cell reference of the first error it finds? (I'm sure there is a way! I just dont know it)

View 9 Replies View Related

Lookup Data In Several Closed Workbooks

Jun 2, 2006

I would like to create a sheet that looks in a certain folder with several .xls files and pulls data from certain cells ( not necessarily next to each other) automatically with out opening each workbook in the target folder.

Each workbook is laid out the same, based on a template, and emailed to me each day (I'll actually get 3 or 4 a day, one from each plant) So the number of files will grow every day. (I've read about a way to automatically save the file based and the date, hour, and minute, and email the file when a certain condition is met, and plan to use this technique in the template.) So the file names should be consistent.

As an example, I would like cell A1 to contain the date from the oldest file and increment row by row as the files come in, automatically. Then the cells B1 on across would contain data from certain cells within the .xls file with that date and time.

Every time that workbook is opened it would update to include data from any new files in the specified folder.

I've done some basic vlookup formulas to look at one particular workbook but not several.

View 9 Replies View Related

Lookup Matching Data In Other Workbooks

Oct 24, 2006

I am looking for a way to add cells to a master workbook from matching cells in various other workbooks. The master will have the complete list of store numbers. The other files will just show the store number that had returns. I need to add a new column to the master workbook for each of the other files. If that makes sense?? So the master report header row will have: storeno,storename,3-1-06,3-15-06,4-1-06,4-15-06, etc..... Then column a will be a listing of the store number 1 -200.

View 2 Replies View Related

Search Function (locate Data) Search All Sheets Within The Workbook

Sep 14, 2009

Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.

Public Sub FindText()
'Run from standard module, like: Module1.

Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer

myText = InputBox("Enter the text that you want to search for:", "Start Search!")

If myText = "" Then Exit Sub...................

View 9 Replies View Related

Lookup Function To Lookup For Data In Another Table

Jul 29, 2008

I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.

View 9 Replies View Related

Locate/Lookup Values That Exist In 2 Lists

Aug 25, 2006

I have a list of last names in Sheet1!Column A. I want to see if any of those exist in Sheet2!Column A, and if it does I want to post the data from Sheet2!Column B into Sheet1!Column B.

View 2 Replies View Related

Use IF/Lookup Function In Multiple Criteria

Apr 30, 2009

I have table product that include (wide 1,wide 2,thick 1,thick 2,long 1,long 2,Price) And I have table transaction than have wide, thick, long and price)

I want that price in table transaction fill automaticly from table product where ( wide between wide 1 and wide 2 ) and
( thick between thick 1 and thick 2 ) and ( long between long 1 and long 2 )



View 2 Replies View Related

How To Use Lookup Function With Multiple Criterias

Aug 26, 2009

I've forgotten all the formulas that I learned during college. And that was for Excel 2003; now I'm using Excel 2007. So, hopefully you experts can help me out.

So here's the problem. I need to set up an invoice in Excel but I don't know what formula to use. I'll first describe my invoice and then I'll tell you the problem.

It's a basic invoice where you have customer's info and product's details and prices. So, when I choose a customer's name from a list in a cell, the address and phone of that customer will automatically appear. I don't have a problem at all with this part. And then there's the product details section where you enter a product type, size, color, price per unit, and total price. The price per unit also depends on the customer entered. Each customer has different pricing and I used a discount system for each customer. We basically have a universal price list for our products, but we have different discount percentage for each customer.

My invoice will look something like this: ...

View 10 Replies View Related

Lookup Function On Multiple Criteria

Mar 21, 2012

I have two documents, and I need to search data in document 1 based on information given in document 2. On document 1 I have ID number and date range and on document 2 I have ID number and date. Date needs to be in the date range, and ID numbers must be the same.

Please see tables below for the reference.

Document 1
Document 2 ID Start date End Date Data
ID Date Data 11 01/01/2009 31/12/2010 Data 1
11 05/05/2011 22 01/01/2010 31/12/2010 Data 2
22 08/08/2010 22 01/01/2011 31/12/2011 Data 3
33 01/01/2012 33 01/01/2012 31/12/2012 Data 4
11 02/02/2009 33 01/01/2010 31/12/2011 Data 3

11 01/02/2011 31/12/2011 Data 2

View 2 Replies View Related

MLOOKUP() - Function For Multiple LookUp

Aug 13, 2007

a multiple lookup several times on this forum.

Code and example on

View 9 Replies View Related

Lookup Function Based On Multiple Cells

Jul 21, 2005

in the lookup function listed below, it only produces a result for the first exact macth. well, what if the data i am searching on could result in multiple results?
i would like advise on how to modify this formula to bring back the 2nd, 3rd, 4th, etc instance of a match in the lookup array.

the original formula , i found on this site, allows you to combine multiple cells to create a unique reference, and then search in another table containing columns of those combined cells, and results in the intersection of a row and column and that is the data you want returned ....
kind of like a hlookup, but you can use one or more cells to create the unique lookup, and bring back whatever column you want (where as a hlookup is limited to searching the left most column and bring back a column to the left)

here is the formula:

{=INDEX(data to retrieve,MATCH(cell1&cell2,table to find cell1 in&table to find cell2 in,0))}

"data to retrieve" is an array of one column containing the data you want to retrieve
"cell1&cell2" is a combination of cells to make the unique lookup (this could just be one cell if you wanted)
"table to find cell1 in&table to find cell2 in" is a table array to look in to find the look up value

View 9 Replies View Related

Lookup Function/Formula Across Multiple Worksheets

May 14, 2008

This formula works on 1 sheet but we are unable to make it work looking across multiple worksheets? Looking for a match to A3 in column G and wanting to pull the info from column E in the same row.

=IF('2'!G3:G271=A3, LOOKUP(A3,'2'!G3:G271,'2'!E3:E271),0)

View 8 Replies View Related

Formula / Function To Pull In Multiple Lookup Values

Aug 21, 2014

I am trying to replicate a payslip from a list of data on a worksheet.

The list of data contains the employee name, location they worked, and number of hours.

Each employee will work at multiple locations throughout the month, perhaps 10 or so.

The payslip must contain each location worked along with the relevant data, in a list so to speak.

What function can I use to pull this in? Of course if it were one location I would use vlookups to pull in data. As this only returns the top match I would then need a different formula to pull in the second location in the cell underneath?

View 6 Replies View Related

Using Lookup Function To Combine Text From Multiple Rows

Oct 4, 2012

At a high level, I have several thousand rows of data. For the sake of simplicity, assume there are two columns: 1) Name; and 2)A comment (optional, could be blank). See below for example..

Sheet 1
Row 1: Mike │ "Great to work with"
Row 2: Mike │ "Bad manager"
Row 3: Tom │ "Great guy"
Row 4: Mike │ (blank)
Row 5: John │ "Cool"
Row 6: Mike │ "Best boss"

On a separate sheet, I want to be able to somehow use a lookup function to combine all the comments for each name in a nicely formatted package (notice how I skipped the blank space so it wouldn't take up a line.

Sheet 2

="Comments for "&[ref to cell containing "Mike" in another sheet]
- Great to work with
- Bad manager
- Best boss

View 6 Replies View Related

Lookup Function Required To Return Multiple Matches

Apr 24, 2007

I have a list with 8 columns. In the majority of instances, Columns A & B will have duplicate information. I need to be able to search columns A & B & return the information from them PLUS all info contained on the same row's from columns C/D/E/F/G & H. EG:...

My column headings are as follows...
Type Region Contact Phone Email ADD1 ADD2 ADD3

In column A (type) would be the type of contact eg:business/personal/family etc.

So... If looking for family I'd need excel to show me the region, contact, phone, email, add1, add2 & add3 for each family record. Hope this makes sense!
There are over 1000 records i need excel to search through & return matches.

I'm an intermediate excel user at best so finding it complicated. Tried vlookup but that stops when it finds a match & anything else seems over my head.

View 9 Replies View Related

Lookup Type Function Returning Multiple Values

May 23, 2007

I have attached a sample spreadsheet which describes my problem.

In C2 and C3 under the first business coalitions column, I have written in text what I would like to be displayed automatically with a formula. The trouble is that, as you can see, Abbott Laboratories is a member of 3 business coalitions. Therefore, a simple vlookup doesn't work because that would only return 1 result.

View 5 Replies View Related

How To Use MATCH Function - Locate Exact Name

Apr 4, 2014

I'm trying to use the MATCH function. I want to locate an exact name. I used the formula:

=MATCH(A6,A3:A10,0). This is the name cell I want to retrieve, the column in which the information is found, and 0 for the exact match. The return answer I get is 4.

View 3 Replies View Related

Function VLOOKUP At VBA Program Inside A FOR Loop

Apr 17, 2009

I want to use the function VLOOKUP at my VBA program inside a FOR Loop

View 3 Replies View Related

Consolidate All Data In Multiple Worksheets Of Multiple Workbooks In One Master File?

Jul 12, 2014

I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.

All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.

I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...

View 4 Replies View Related

Summing Data From Multiple Workbooks Based On Multiple Criteria

May 14, 2014

I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.


Location A Class:AA Segment: Sports Bar Rep: John Smith

Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1

I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.

I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.

View 2 Replies View Related

Data Matching And Import Function From 2 Workbooks Into Main

Aug 25, 2007

Im creating a cash reconciliation application for a BUS operator client that has 6 depots and 1500 drivers.

each driver is uniquely identified by a special number.

long story short, I get 2 workbooks (named Cash1 & Cash2) that I need to have imported by driver number in my main cash rec workbook named Cashrec.

for example; ...

View 9 Replies View Related

Copyrights 2005-15, All rights reserved