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
ADVERTISEMENT
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
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
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
View Related
Apr 24, 2009
LOOKUP for 2 different workbooks. I have been using the following codes to do the task.
View 2 Replies
View Related
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
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
etc
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
Dec 19, 2007
I have two workbooks with the same sheet names.
One workbook is called "Main" and the other is called "Subset".
Column D in each sheet is mostly identical between the two workbooks and contains the LOOKUP references.
I would need a macro (called from the "Main" workbook) which allows the user to browse for the "Subset" workbook.
Once selected, the macro should cycle through all sheets of the "Subset" workbook and for column D values LOOK them UP in "Main" workbook and transfer the adjacent values from column C of the "Subset" workbook to column C of the "Main" workbook.
Appropriate error handlers need to be in place for:
- Selecting the appropriate workbook (i.e. one which contains identical sheet names)
- The "Main" workbook is likely to contain additional sheets which should be ignored by the macro
- Column D LOOKUP entries which are not found in the "Main" workbooki should be ignored (and vice-versa)
View 9 Replies
View Related
May 2, 2008
I have a spreadheet which requires data from another spreadsheet. Spreadsheet 1 is only project numbers, spreadsheet 2 has all the customer data in it. I have used a 'vlookup' function to find the project number and copy the appropriate information over. The problem I have is the projects are a 6 number format (eg 531300) in both spreadsheets. In the sheet I want to complete, the project numbers only match the first 4 numbers, as they are seperated into sub-projects. Therefore the data in the data spreadsheet will only have a row for 531300, yet the one I fill in might have rows 531300, 531301, 531302 etc. Is there a way to run the 'vlookup' and only match the first 4 numbers. The current formula I have in the first cell is as follows: =VLOOKUP(A4,'[Project Database_Updated 04-30-08.xls]Sheet1'!$A$1:$E$1007,2,FALSE)
View 3 Replies
View Related
Aug 21, 2009
I have a standard template worksheet called "Input" (the workbooks name is "Input Capture") which spans C12:U1100.
In addition I have a seperate worksheet which is based in an all entirely seperate workbook called "People Data" (the workbooks name is "Succession Planning") which again spans C12:U1100.
What I would ideally like is a macro which matches the value in column C and populates the entire row (much in the same way as a HLOOKUP, only pasting values) with the exception of columns M and Q.
View 9 Replies
View Related
Aug 23, 2008
I have 2 reports with the same column headings. I want Excel to compare each one on the common OrderID and then just show me the ones that don't match--either one invoice can't be found on the other, or in the cases where they are found, the invoice amounts do not match (IOW create an exception report).
View 2 Replies
View Related
Apr 7, 2008
I have two workbooks, one with two sheets and the other the new workbook where I want to copy the cell values. What I am trying to accomplish is:
1. From Workbook book1. sheet2. cellb (dynamic row values) I want to look for the value in book1.sheet1.cellb (static row values). If it is found I want to copy the cell values from book1.sheet2.cellb, celld, and celle to book2.sheet1.cellb, celld, and cellg. If there in a new value in book1.sheet2.cellb, I want to copy the same values (cellb, cellc, celld,celle) to book2.newrow.
I am attching the two workbooks with sample data, for clarification.
View 3 Replies
View Related
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
Nov 28, 2006
here is an example....
(this is on a sheet called Summary)
----A--------B --------C------- D
1Names----At Bats----Hits----Batting Average
2Tom-------38--------31------.816
3Derek------19--------14------.737
4Joey-------40--------28------.700
5Chris-------40--------27------.675
6Chuck------37--------24------.649
Using the LARGE function, Excel has created a list based on batting averages(on a separate sheet called Line-up). It looks like this.....
---A----------B
1Names----Averages
2 -----------.737
3 -----------.700
4 -----------.675
5 -----------.816
6 -----------.649
The problem I am having is figuring a way for Excel to also bring the corresponding names (after using the LARGE function to create the line up list).
View 3 Replies
View Related
Jun 12, 2007
I have a very large spreadsheet of customer information(I call it the master spreadsheet). Each row contains only 3 things: Account number, product bought, Price
Later I receive the money from the customer for that product(the pay sheet) that contains the exact same thing in the same order: Account number, Product bought, Price paid.
What I'm trying to do is compare the two spreadsheets so that when i receive the pay sheet of cusomters who have paid with the amount it will deduct it from the master sheet.
So it should compare account numbers when it finds a match then it should subtract the amount paid (column C) from the master spreadheet price column(column C also).
sometimes customers don't pay the right price so it has to be a subtraction so I can see if it was over paied, underpaid etc.
Right now I'm still doing it manually combining the two documents sorting it by account number and checking for matches in column A (account number).
View 5 Replies
View Related
Jan 28, 2009
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View 3 Replies
View Related
Aug 2, 2013
I have three source workbooks that needed to be consolidated into a master workbook pasting the data with values & formatting.
The Master workbook also contains 6 additional worksheets that link to the data pulled in from the source files.
My question is how do I write the code so the source files populate the master in a specific order.
For example, the 3 source files are named "Central" "NED" and "WEST" and I need them to populate the master workbook in that order.
This is the code I am using to consolidate the data:
Option Explicit
Sub ConsolidateSheetsFromWorkbooks()
'Author: Jennifer Starr
'Date: 7/12/2013
'Summary: Open all files in a folder and merge data (stacked) on all
[Code] .....
View 1 Replies
View Related
Mar 13, 2007
I have data in 2 workbooks. Book1 - Sheet1 - Col A and Book2 - Sheet2 - Col A. I want to compare Book2 with Book1 and list all values in Book2 that do not find a match in Book1. I want to run a macro for this
View 7 Replies
View Related
Jul 16, 2014
You have two Workbooks open. Workbook 1 and Workbook 2.
You COPY all the data from Workbook 1 and PASTE the data as a 'Link' into Workbook 2.
You then save and close both Workbooks.
You then open Workbook 1 and edit the data. You save and close Workbook 1.
QUESTION
Will the linked data in Workbook 2 update automatically (i.e., without opening the file)?
View 4 Replies
View Related
Aug 1, 2014
I have an excel spreadsheet supplied by a client, let's call this the master sheet, with about 500 URLs and I've been asked to gather information from Google Analytics and place in a spreadsheet. This spreadsheet can't be changed as it goes into their CRM system.
On the master sheet, we have a list of URLS, what I need to do is go through each URL and place the number of visits to each URL on a weekly basis.
Google Analytics excel spreadsheet, will spit out a list of URLs and the page visit numbers.
My question is:
If I have both spreadsheets open, the master spreadsheet and the information from Google Analytics spreadsheet. Is there a way of having both sheets open and doing an exact match on the URLs between both spreadsheets, so we can copy and paste the information directly.
Or even a formula that says something like:
If the master sheet spread has the same URL in the Google Analytics spreadsheet, paste the relevant information into the correct cells?
As you can see, doing this manually will take a long time. I'm just looking for a productive method to save time.
View 2 Replies
View Related
Aug 5, 2014
Itransferring data across two work books I have.
Every month I get sent sales figures of products, from now I have been typing in every sales figure which is long and boring. So I was wondering whether there is a way to make excel match the products on each workbook (mine and the one I get sent) seeing as there is a product number on both workbooks and then have excel automatically fill in the field I type manually which are quantity and value. So excel would see on the one workbook that product X has sold 10 units with 1000 value and then would go across to my workbook and fill that in the area I need on mine matching from the product code?
View 6 Replies
View Related
Jan 23, 2009
I have to workbooks and I want to populate one of them with data from the other. The receiving document has the fields Account and SubAccount which I need to match up with the account and subaccounts in the other workbook.
However, in the other workbook, the line looks like "1000 Wages 000001" (which is account, name, subaccount). I can't find out how to have my receiving document scanning the providing document and when it finds an account and subaccount (in the above string) that matches the account and subaccount in the receiving document, to bring over the data.
View 2 Replies
View Related
Dec 13, 2013
I recieve a data file on a monthly basis. Is there a way to take all of the monthly worksheets and combine into one workbook without doing a copy paste every month?
View 1 Replies
View Related
Oct 11, 2008
i had received a new project. i want to compare the data among 2 different workbooks. there are book1 and book2 which i attach at here. if there are 1 cells is different with both workbooks, then the whole row will copy and paste to a new workbook "output.xls".
i also attach the "output.xls" at here.
the background color i highlighted which data is different, just for easy to view. so can ignore the color.
View 14 Replies
View Related
Dec 3, 2008
A while back someone helped me out with a code to pull data out of all workbooks in a folder.
View 10 Replies
View Related
Mar 6, 2009
Look in folder and find workbooks with date in name:
Date format is MMDDYYYY
Workbooks have multipule sheets
Look at columns in worksheet E and G.
If E >=10 and G is "ENABLE" copy A:G and
the tab name and paste in the workbook that has
this VB code.
What I am going to do is create a workbook
with a button on Sheet1 and put your code
behind the button that will tackle this great
feet that I do manually that takes about
3 days to do.
View 9 Replies
View Related
Apr 16, 2009
Is it possible too do this if they are on the same computer. Lets say I want all the data on page3 in one workbook, data on page2 in another and have it show up in another workbook. If this can be done can it delete cell entry that match?
View 14 Replies
View Related
Jul 22, 2009
I'm trying to copy data from several workbooks into another - in Excel 2003. The following code works perfectly except when there is only the header row in the target workbook. Then I get the following error message, "Run time error 1004. Application defined or Object defined error". How can I get it to find A2 the first time - without coding A2 in?
View 4 Replies
View Related
Dec 14, 2009
Is it possible when i am in my current workbook to refer to the value in another cell in another workbook within a formula?
Example:
I have a workbook named "Sample1.xls" that contains a spreadsheet "revenue" and in cell C24 is the value that i want to have in my current workbook.
View 2 Replies
View Related
Jul 11, 2014
I am working on a project that requires me to copy columns from multiple workbooks to a main one. I am a new comer to VBA what I could come up with gives me an error. Here's where I am right now:
Code:
Sub Import_Click()
Dim MainFile As Workbook
Dim ComFile As Workbook
Dim RDFile As Workbook
Dim UTIFile As Workbook
MainFile = Dir("C:ModelingProcess FilesMain File.xlsm")
ComFile = Dir("C:ModelingProcess FilesModeling - Commercial.xlsx")
[code].....
View 3 Replies
View Related
