Cross-referencing Two Lists From Separate Sheets?

Jan 16, 2014

I'm working with several sheets, each containing databases of customers that bought different products. I'm trying to do a cross reference so I know which customers has already bought multiple products.

English is not my first language and I might not be able to clearly express what my problem is, so I included a worksheet example.

In the example, you can see that Lea bought an apartment coded with APRT-001 from the first sheet. Lea also bought a condo coded with COND-002. What I'm trying to do is to put a note beside the her entry in the apartment that she has bought COND-002. And vice versa on the COND-002 entry on the second sheet.

What I usually do is combine all the data in one sheet, use Conditional Formatting to point out the duplicate name entries, and work through the whole thing manually. The thing is, there are thousands of names and I need to update the list regularly

View 5 Replies


ADVERTISEMENT

Cross Referencing Between Sheets

May 2, 2006

In my application, I am reading a barcode label whose first three digits are a code for the model number (stored in Sheet1, Column C). On sheet 2, this 3 digit code appears in Column C also, and the exact model number for each 3 digit code is stored in Column A. I would like to display the information from Sheet 2 Column A in Sheet 1 Column H, depending on the value of Sheet 1 Column C. (for example, Sheet 1 Column C contains the code 011. On Sheet 2, Code 011 is the code for Model # HTE14WABWW. I would like to copy HTE14WABWW to column H on every row where the row's column C equals 011.

I see several examples of sorting data across sheets but nothing that really answers my specific question.

View 5 Replies View Related

Cross-referencing Two Spreadsheets

Jun 20, 2014

Basically I order thrice a week from a certain supplier to fill the shelves of my shop.

They make available to me two spreadsheet dumps: an orders spreadsheet, with the following columns:-

order ref
date
unique product code
description
qty

9262
03/07/2013
622642
Fabric Dye
4

And an invoices spreadsheet with the following columns:-

invoice ref
date
unique product code
description
qty

122551
04/07/2013
622642
Fabric Dye
4

Basically I can see the potential here to find out what we've ordered but that which they haven't sent which would be so handy.

I have tried to vlookup() produce code against product code, this kind of works but below are some example drawbacks:-

1) no idea if I've ordered it ten times and they've only not sent it once, or if I've ordered it ten times and they've not sent it nine times.
2) no idea of dates - i.e. ordered this in June '13, October '13 and February '14 and they sent it in February '14 only.

View 5 Replies View Related

Cross-referencing Lookup?

Jul 28, 2008

I am trying to match a person's name in the A column (A3) with particular course requirements in Columns B through to BB (ie. B2). Not every person will have taken every course. I have the master list of data on another worksheet with 4 columns of data: A-Student Name, B-Course Name, C-Final Mark, D-Additional Notes.

What I would like to do is have Excel find the student name on sheet one as well as each of the courses listed from B-BB and check sheet two (master data) to find if the student name and course name appear on the same line. If it does I want it to return the value in column C (the final Mark).

I thought that I could do this with VLOOKUP but it only works with the left most column correct? I saw something about a VLOOKUP2 but couldn't find that option in Excel 2007.

View 13 Replies View Related

Cross Referencing Cells

Jan 16, 2014

Should I use the 'IF' or 'LOOKUP' functions to fill a cell based on the content of another?

If C2 contains 'Cork' I need D2 to read 'Munster'. Indeed I will need D2 to read 'Munster' for a number of different C2 values.

View 4 Replies View Related

Cross Referencing Between Two Worksheets

Mar 5, 2014

I need to set up a system for recording data into a certain way.

I a currently using this formula =IF(sheet1!A1:A20="H","Y","") and it kind of works.

Basically i want it to fill a cell in sheet2 with a "Y" if ony one cell between A1 and A20 from sheet1 contain a H. At the moment it only works if a few of the cell between A1 and A20 contain "H".

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

Cross Referencing Names On Different Worksheets

Apr 23, 2009

I have three different sheets, each with a column of names, titled "list1, list2, list3." I also have a fourth list of names on a sheet titled "masterlist." I am wondering if there is any way I could cross check the three lists against the master list to see if any names appear on the master list but do not appear on any of the three lists. Is there any way I can cross reference the master list with each of these lists?

View 2 Replies View Related

How To Cross Reference Two Lists

Apr 4, 2014

I have two lists of reference numbers; I need to see if any of the references appear in the other list. I don't want them to be removed, maybe highlighted?

I have made them into one list and used the duplicate conditional formatting but some references appear twice in one list and there is a lot of data so this is not good enough.

View 4 Replies View Related

Cross Referencing Cells To Input Values

Aug 4, 2009

I am attempting to cross reference sheets to match up IPs to building names. I have attached an example. Essentially, I am trying to take column E on the TEST sheet and match it to column A on the IPs sheet. Whatever the Building Name (column B, IPs sheet) is, I want that filled into Column G on the TEST sheet. This would be a very labor-intensive process if it cannot be done automatically.

View 3 Replies View Related

Cross Referencing: Know How Many Of Each Item In The Same Type Of Material And Size, Need To Be Made

Feb 5, 2010

I have a list of products that have individual item numbers that share some common sizes and some common materials and are ordered in different quantities. When it comes to re-ordering the raw material to remake these items, I need to know how many of each item in the same type of material and size, need to be made. The attached spreadsheet shows more clearly what I am probably failing to explain! I have tried various combinations of VLOOKUP combined with MATCH but I can’t get anything to work – I guess I’m getting a little punch drunk here having spent the whole morning trying to find a solution!

View 5 Replies View Related

Creating A Validation List, Referencing Other Lists, Remove Dups

Jun 25, 2006

I tried using several of the formulas and VBA codes from other posts, but they just didn't work out quite right. And since I'm worthless at VBA coding, my changes either did nothing, ruined the code entirely, or blew up my computer : D . The attached doc should explain everything.

View 9 Replies View Related

Cross Check Data In Two Sheets And Delete If It Matches

Mar 29, 2014

creating a macro I have two sheets named customer list (I have only put in 30 rows as an example but some sheets have 400 rows)

in the sheet named list column "F" are the names it should be cross checked with column "F" in the sheet named customer if it is there entire row to be deleted

View 3 Replies View Related

Referencing Entire Row On Separate Worksheet If Criteria Met In Specified Column

Sep 22, 2013

I am attempting to create a workbook that will keep an inventory of items in a catalog that I own and want to order (worksheet1). Ultimately an order form would be filled in(on worksheet2). I would like to have the entire row of data copied (from worksheet1)to the next open/empty row on the order form (worksheet2), if any cell in column C (worksheet1) has data.

I have attached the workbook : Catalog Orders.xlsx‎

View 4 Replies View Related

Copy Rows Between 2 Sheets Based On Cross Reference Match

Apr 22, 2008

I am trying to drop (paste) in new data in a range in sheet 2 and cross reference a column with a table in sheet three and display all rows of matching instances in sheet 1. Example:

Sheet 3 has
a1 b1
amcdap amber connor
apsdap ashley simpson


sheet 2 has
a1 b1 c1 d1 e1
amcdap 300 400 2:00 9:00
apsdap 500 300 4:00 8:00
capdap 200 300 5:00 9:00
dlsdap 400 300 2:00 8:00

I need to return only rows 1 and 2 to sheet 1. I guess ideally I'd like to drop data in sheet 2, click button.

View 5 Replies View Related

Comparing Three Lists - Identify In 3 Separate Columns

Jul 9, 2013

I am working on a project, I have three different lists that I want to compare and I want to identify in 3 separate columns which titles are found on which list.

For example:

Title List 1List 2List 3
ACAROLOGIA x
ACCIDENT ANALYSIS AND PREVENTION x
ACROCEPHALUS x
ACS CHEMICAL BIOLOGY x x x
ACS CHEMICAL NEUROSCIENCE x x x
ACS COMBINATORIAL SCIENCE x x
ACS MEDICINAL CHEMISTRY LETTERS x x
ACS SYNTHETIC BIOLOGY x x x

View 4 Replies View Related

Sorting List Of Items Into 2 Separate Lists

Oct 25, 2011

I work for a printing company that prints labels. We have 6 printing presses, lets call them A,B,C,D,E & F. Certain labels can only be printed on printing press F.

From our business system I can run a report in excel format giving details of all the labels that we have sold in the present month. I need split that report into 2 separate excel spreadsheets; 1 showing everything that was printed on presses A to E and the other showing everything printed on press F.

I also have another excel spreadsheet which gives a complete list of all the labels that have to be printed on press F.

Every label that we print has a part code and both spreadsheets show this part code so that would be the linking factor between the 2 sheets.

View 1 Replies View Related

Find The Minimum Distance Between Two Points Using Two Separate Lists?

Jul 5, 2011

Is it possible to find the minimum distance between two points using two separate lists of different lengths without creating separate columns of equal row sizes

? I am trying to find the shortest distance form one point to another which is a fixed value. I am using the standard distance formula: =SQRT(((X2-X1)^2)+((Y2-Y1)^2)+((Z2-Z1)^2)).

My X1,Y1, Z1, has 2,000 rows in three columns that need to be compared to find the minimum distance from my one row/column of X2,Y2,Z2.

Is there a command that could go through and use each row of the X1, Y1, Z1, while looking for the MIN of the above formula without having to create an equal amount of rows for the X2, Y2, Z2?

I know I could do it by running this out the long way and then finding the min after I do all the math but I am wondering if there is an automated way for Excel to go through and do the forumla and then only return the MIN value?

View 8 Replies View Related

Rearranging Data From Combined List To Separate Lists?

Aug 21, 2014

Is there a simple way via VBA to alter the layout of data from a mixed up two column list into multiple headed lists on another worksheet (within the same workbook)?

The attachment should better demonstrate what I mean. Sheet1 has example data of how it is and Sheet2 shows how I would like it.

The data will be dynamic in the sense the numbers of unique values in column A will change (only increase, never decrease), as will the number of unique values in column B.

View 2 Replies View Related

Referencing: Export File Arranges The Jobs In Rows With Each Value In A Separate Cell

May 3, 2007

I have a quoting application for damaged vehicles, this application is able to export all quotes and information relating to quotes into an excel sheet. the export file arranges the jobs in rows with each value in a separate cell i.e.

Job #|| Name || number || Parts $ || Total $
1453 || Mike K || 1234567 || 123.00 || 222.00
1533 || John C || 4353456 || 234.00 || 2342.00

Etc

Each job is listed on a separate row as it exports the records from an access database. once the information is exported I need to open another workbook and type in a job number into a cell and have excel automatically reference the relevant job and information relating to that job (all info in the same row) and fill in all the details from the other workbook into the new workbook. Example: the exported file contains 200 jobs, of those 200 i require 20 to view for one week. I have the job numbers of the jobs which I need to get information for. i open up another workbook and type in the 20 job numbers in separate rows and excel fills in the rest by referencing the other worksheet.

View 4 Replies View Related

Referencing Multiple Sheets And Sum?

Mar 21, 2014

There are 4 sheets in workbook.

Sheet name is 3-12, 3-13, 3-14, 3-17.

that 3-15, 3-16 is not because of holidays. But, I want to display in 3-17 sheet sum of A1 cell from 3-12 sheet to 3-16 sheet.

nearly, =SUM('3-12:3-16'!A1)

=SUMPRODUCT(N(INDIRECT("'3-"&ROW(INDIRECT("12:"&RIGHT(A1,2)-3)) &"'!A6")))
is well work.. but

=SUMPRODUCT(N(INDIRECT("'3-"&ROW(INDIRECT("12:"&RIGHT(A1,2)-1)) &"'!A6")))
do not work..

I want to sum dynamic skip error value. namely, calculating to yesterday(3-16) from first day(3-12)...

View 11 Replies View Related

Referencing Multiple Sheets In One?

Jun 4, 2014

I am currently referencing data from 20 different sheets (11 columns each, varying amount of rows) all on one sheet. I intend to edit random data on each individual sheet with the added possibility that I will be adding or deleting rows....

So my question is, how can I set up this consolidated sheet so that when I delete/add a column in the referenced sheets, it will automatically add or delete these (additional) rows?

I have it set to the range of rows that I currently have, but all together I have about 1500 rows and I do not want to go through all of these rows deleting anything blank or adding in more. This consolidated data also is also filtered so finding out where to add to any ranges would be difficult unless I wanted to "un-filter" them (which I do not).

View 1 Replies View Related

Formula Is Referencing Different Sheets

Nov 16, 2006

I am having trouble with the following formula in my workbook with many sheets: ="='"&MONTH(A6)&"-"&DAY(A6)&"'!"&"$B$13". I entered this formula into B6 of the current sheet. Cells A6:A33 contains a date (02-Jan-07). So what I'm trying to do is retrieve the value in B13 of the sheet corresponding to the month and day in A6. If I hard code this formula into B6 ='1-2'!$B$13 it works but I don't want to edit each of the formulas in column B to correspond to the correct sheet. When I type in the above concatenated formula it just returns text (='1-2'!$B$13). Is there a way to do this so the formula will actually execute?

View 3 Replies View Related

Referencing Cells On Different Sheets

Oct 14, 2007

I currently have 3 sheets 1, 2,and 3.

Data is input onto sheet 1, this is copied into sheets 2, no real problem there. However i need to enter data onto sheet 3 and if that data appears on sheet 2 i want the line, not just the cell to be coloured on sheet 2. The data on sheet 3 will not be in any specific order in the column

I have tried conditional formatting but keep getting a message to say it cant be done and offering a formula which i dont understand.

View 9 Replies View Related

Referencing Cells In Other Sheets Using INDIRECT

Aug 6, 2014

Applying my indirect formula to the rest of the cells in my sheet properly.

I am getting the values of cells in other sheets, using INDIRECT to use cell values to name the sheets. Here is an example of how the cells are now...

A
B

1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))

[Code] ....

The formulas in B1 are going to return the value of A5 in "Sheet 1" (or whatever value is in A1) and B2 will return the vale cell A5 in 'Sheet 2. The next line will be the value of A6 in Sheet 1, and the next will be A6 in Sheet 2 and so on.

So I have quite a few rows to get through I want to be able to autofill so it looks like this...

A
B

1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))

2
Sheet 2
=(INDIRECT("'"&A2&"'!$A$5"))

[Code] ......

As it is now, when I autofill, it autofills the Sheet name part of it. It is looking like this...

A
B

1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))

[Code] ......

Excel isn't intuitive enough to know I want to move cells in the referenced sheet.

View 5 Replies View Related

Creating Sheets While Referencing A New Row In Some Cells

Jul 24, 2009

I am trying to find a way to create new sheets in a workbook that when created will reference a new row on a data sheet in that workbook. Each workbook could end up containing varying numbers of sheets (anywhere from 2 to 150+) based on size limitations. There are some lookups and referenced data on each sheet that would be copied as is and not reference the data sheet. I have not included these in my example.

I'm trying to avoid having to change each cell's reference manually for all of these sheets (which is one of the solutions I've come up with so far). My current method of creating these workbooks does not involve the data sheet and each workbook starts off with data only on sheet1. I select and copy that sheet into sheet2 and every subsequent sheet. I want to incorporate the data sheet so it can be filled out by someone else and copied into that tab to populate all of the data sheets.

View 13 Replies View Related

Adding Rows To Sheets With 3D Referencing?

Jun 13, 2013

I have attached a file for reference. I have another file I use that is similar to the one attached, however it has 500+ rows and is still growing. The issue I am having is that in this file I want to make a "Template" worksheet. Basically I need 50ish sheets that are more or less the same. I have a column that describes a item, a items weight, a row with headers for the columns that I want to appear identical on every worksheet. I want the variables to be the count. These will change from sheet to sheet with formulas to than update the totals.

My issue I am having is that I constantly find myself needing to insert new rows for new items. In my real worksheet I have rows grouped together in a logical way so the rows cannot just always be placed in the next available row at the end of a sheet. How I can have sheet 1 be my "template" and the others take their item lists and weights from sheet 1. In the example file I created sheet 1. I than created sheet 2 and selected a cell entered "=" and selected the cell that I want it to have the same info from on sheet one. I fill handled these items down to generate a list. I than held down control and dragged a couple of new sheets. This process works, and is what I am currently doing, but what do I do when I wish to add a new rob between row 4 & 5?

View 2 Replies View Related

Referencing Sheets Titled As Dates?

Oct 3, 2008

I have a workbook with probably 60 tabs/sheets at the bottom, each titled with a date, like 10-2-2008. In each tab/sheet, I have information for that day. What I am trying to do is on a new sheet, have all those sheets listed down column A. Next, in column B, I would like the value of cell D1 from each tab/sheet.

Is there a formula I can write that will lookup the value of D1, for each date listed in column A? I have tried doing =INDIRECT(A1&"!D1"), where A1 is a date/tab name, but it will not work. If however I rename the sheet "5", then have a "5" in cell A1, it will give me the data listed, but for some reason if I have a date with dash's, 10-2-2008 it will not work.

View 4 Replies View Related

Referencing Listbox Items And Sheets By Name

Jan 24, 2014

Code:
Dim flag As Boolean

Private Sub UserForm_Initialize()
Dim myWorksheet As Worksheet

flag = True

[Code] ........

I made a user form that, with the click of an item in the listbox (which is populated with the worksheet names), will show or hide the matching worksheet. I was using indices, but a problem occurs when there are some hidden sheets to begin with, because the listbox is filled with only the visible worksheets (that's part of the functionality required) and so, when there are hidden worksheets the indices of the listbox are totally different. So, to avoid that kind of bugs I thought I'd use the sheet + listbox names to show/hide the worksheets, but that is proving to be a very hard task.

How I can use the listbox item names + worksheet names to make the "mapping" correctly?

View 5 Replies View Related

Referencing Cells In Other Sheets Using Address Function

Feb 7, 2012

I'm building a table that references a dynamic range on a pivot table. So far I've gotten the function to return the address on the pivot table but it wont return the value. I'm doing this so that I can both refresh the two source data sets and summarize (pivot table) and also graph these two summarized data sets since you can't graph two different pivot tables together.

This is my code so far:
Iferror - To return zero if the source data doesn't exist yet
Address - To concatenate the match functions
Match - to find the row and column numbers on sheet "B" that match the x,y call on the original sheet "A"

=IFERROR((ADDRESS((MATCH($A2,'Install Data Pivot'!$A:$A,0)),MATCH(B$1,'Install Data Pivot'!$4:$4,0),1,1)),0)

Returns $B$5 since this is the cell where the information lies.

I've tried just adding the sheet name like this...

=IFERROR('Install Data Pivot'!(ADDRESS((MATCH($A2,'Install Data Pivot'!$A:$A,0)),MATCH(B$1,'Install Data Pivot'!$4:$4,0),1,1)),0)

But that doesn't work. I've also tried using different variations of Indirect and Index but I still can't get it to reference the other sheet.

View 1 Replies View Related







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