VBA Vlookup From Another File
Dec 3, 2009
I am working on a new project and am trying to use Vlookup in VBA.
The code i have so far is:
For Each cell In Range("B2:B" & CurrRow)
cell.Value = Application.WorksheetFunction.VLookup(cell.Offset(0, -1).Value, Workbooks(Comparefile).Worksheets(CompareActive).Range("A:A"), 1, False)
Next cell
CurrRow is the last row in the active sheet.
I have a workbook defined as Comparefile (not the entire filepath) and a worksheet as CompareActive.
Both files are opened...
The macro works untill this line, i don't get an error message but i just don't get any results...
Is there anything i do wrong? I really can't get it to work.
View 9 Replies
ADVERTISEMENT
Oct 24, 2013
Vlookup from another workbook that changes name daily.
Basically what I want is to put the date in cell M26 (formated YYYYMMDD) and the vlookup would got to YYYYMMDD.XLS and get the data I need.
When I change the value of M26 it will get other data and so on...
This is my current formula:
=IF.ERROR(VLOOKUP(A1;'C:UsersMiguelGoogle DriveVENDAS[20131020.xls]Sheet1'!$B$11:$G$50;6;FALSE);0)
I need to get the data from other days of the month... 20131021, 20131022, 20131023... which I write in cell M26.
I tried the indirect function, but I think I'm failing to get the " ' & in the right positions... Also the 20131021.XLS, 20131022.XLS, 20131023.XLS workbooks will be already opened, so that's not a problem.
View 4 Replies
View Related
Nov 27, 2007
I have 2 files: lets say FILE1.xls and FILE2.xls. In the FILE1.xls i fill in a cell with FILE2.xls name. I want to do a vlookup in the file 1 and to take the name of the file2 from the cell i have filled. for example =VLOOKUP(A1,'[FILE2.xls]Sheet1!$A:$C,3,0) this is the simple vlookup. What i am asking is that i take the file name from the cell. I tried like this: =VLOOKUP(A1,'[=B5]Sheet1!$A:$C,3,0) B5 is the cell with the file name "FILE2.xls". It sais that the name is not correct.
The reason i want to do this is because each day the file name is changed and i want to put in the refference cell the file name and do the vlookup using the file name in that cell.
View 2 Replies
View Related
May 17, 2014
get the name of the File in a Vlookup VBA using wildcard characters. I have to do a VLOOKUP from a specific file which is stored in the same folder as the file in which I need to get this VLOOKUP done, however the name can change anytime keep a word common only for this specific file...
Ex:
I have a File called "Mapping as on 8th May" from where i need to lookup some information for my file and I intend to do this using VLOOKUP in VBA in a Module but since the name can change to anything such as"Mapping as on 16th May" I cant use it in the VBA code, so is there a way around this where If I have this file stored in the same folder then I keep the word "Mapping" specific and unique for all other files in the same folder that the search can pull this file name to be further used in my lookup..
how to use the wildcards in VLOOKUP lookup range for the file name as the columns are going to remain the same...
View 13 Replies
View Related
May 25, 2009
I have a quote that has a value in a few cells =IF(B31="","",VLOOKUP(B31,'[Product Supply-1.xlsm]Sheet1'!$B$8:$N$11,13,0)). Though when i make changes in the product supply file i need it to automaticaly update the quote file. Look at thread http://www.excelforum.com/excel-gene...how-value.html to understand the problem. The quote file is there in thread.
View 4 Replies
View Related
Jan 30, 2013
How to pull data with a VLOOKUP to a file located on a Server.
My big problem is not understanding the syntax of the Server location. I've provided it below (obviously with some character changes).
I have a file located on my Hard Drive. I need to perform a VLOOKUP from A2 on this spreadsheet. The File on the Server is called "LookupTest.xls". The Range of Data is from Sheet 1 and is from A2:C4 and I need to pull the data from column C depending on what is selected from A2 from the file on my HD. I understand how to use VLOOKUP, just not when I'm pointing to another file on a Server.
Server mapping from Windows Explorer:
rbbabc$ on 'RP17409 - ABC Database (AHSSRVVN678.tge.com)'
Again, that server mapping is fake, as I changed it, but this is how it looks on Windows Explorer.
I'm trying to keep a master file of Data on a spreadsheet found on a server so that one master file can be updated, and several other tools pull from that Master File on VLOOKUP, Validation Lists, etc.
View 4 Replies
View Related
Sep 13, 2013
I am trying to build a udf to replicate a vlookup where the table array is in another file.
The scenario is that very often I use store numbers and want to add the associated name quickly without having to open up the reference file and use a vlookup. So I am looking for something like =storename(number)
View 2 Replies
View Related
Jun 17, 2014
I am having difficulty trying to access excel from powerpoint and run a VLookup. I think my problem is somewhere around the xlworkbooks.application.VLookup...(line) Herre is the code i am using below
Code:
Private Sub loginbtn_Click()
If txtUsername.Text = "admin" And txtPassword.Text = "admin" Then
frmMenu2.Show
[Code]....
View 2 Replies
View Related
Jan 9, 2009
I'm using a spreadsheet to assimilate data from a number of different files. Now I need to vlookup on this compiled data, but the vlookup cannot find the value because it is not looking at the value of the data in the cell, but rather the formula. How do I get vlookup to search a column by the value displayed in that cell, rather than the reference to some other file?
View 9 Replies
View Related
Feb 4, 2014
Can I use a vlookup combined with IF function to lookup up to a different file dependent on what the cell says.
For Example cell a1 could have the value Alan Smith, Tony Brown or John Doe dependent on what value is entered the lookup would "lookup" against the file named Alan Smith, Tony Brown or John Doe.
View 6 Replies
View Related
Sep 11, 2009
I have six files that are formatted the same(fld1-1.xlsx, fld1-2.xlsx...). Each file contains an I.D. number (random 25 digit number) in column A and a note (1-9) in column B. The only thing that changes between files is the note column. I want to pull from these six files into a master file. When all six files are combined the note column in the master file is complete, there are no duplicates. Because a blank vlookup returns a zero, could I use an IF formula. Something like IF VLOOKUP FROM data01(ISNUMBER(0), then vlookup in file data02. IF VLOOKUP FROM data02(ISNUMBER(0), then vlookup in file data03.
View 5 Replies
View Related
Aug 29, 2012
I am using below code to open file from 2 diff. folder and trying to do vlookup.
Its working in single loop but not moving to next file
Error: Invalid procedure or call argument.
Sub vFunction()
Dim strTempName As String
Dim strPath As String
Dim wb As Workbook
Dim wb1 As Workbook
Dim strFile As String
Dim strFile1 As String
[Code] ...........
View 2 Replies
View Related
Oct 11, 2012
I am naming raw data files by the date of the data in the file. In another file I am using sumif and sumifs formulas to consolidate the data and organizing it by date, later to copy and paste special to keep the cleaned orgaized data in one spreadsheet. I would like to be able to reference a date and have excel open the appropriate file (named by the date) and then execute the sumif and sumifs formulas. If this is possible I can have the spreadsheet pull in all sorts of historical data all at once and I won't have to worry about copy paste special.
View 1 Replies
View Related
Jun 22, 2013
I will receive one master file which contains the table and it's values and I have another working sheet where I will update the require details. Every time I will copy the entire master data into working file in order to extract the values by using vlookup function. note that the master file has different names each time. So, that if I use vlookup by taking master data path but not getting the results.
I am looking one macro code to select file (master data) and paste the values based on vlooup references in workings sheets. Note this master data file name may change but reference range always same.
View 9 Replies
View Related
Aug 21, 2013
Im working on a document for work, its sort of a learning hobby tbh, something i want to learn. Im trying to do things myself from the books etc, vids from the net. This one i cant find the answer to so before i go ahead i would like to see if it can be done
Basically, we have a few documents that change every day. we get the raw data, and delete coloumns, change the size to autofit width etc...
(im creating a main macro to do that now)
But with that data i need to..
open a new book (but the book name is never the same... book1, book2 as you know)
paste the data
save it
.... the file name! needs to be something like this "Department 20130401" which i saved on 01/04/2013
if i did the document on 10th september.. it would be "Department 20131010"
I have Coloumn A with the dates 01/01/2013 etc
and Colounm B with Department 20130101 etc
Cell ("I1") has =Today() in it
could a macro change the filename from a vlookup result?
View 1 Replies
View Related
Sep 22, 2006
I would like to know if the data saved from a text file to an excel file, would affect the Lookup query.
My problem was vlookup kept returning the same values.
View 9 Replies
View Related
Jun 6, 2013
I have an excel file - central database - that holds a lot of raw data organized in tables. There are more than 15 files (dependent files) that access this data using VLOOKUP formulas.
I now need to add a few columns to the central database and these columns can't be after the columns already existing, i.e. they have to be on the left of some of the existing columns. Inserting those columns is going to mess up all the vlookup formulas in the dependent files.
Is there a way change the central database file without affecting the vlookup formulas in the dependent files?
View 2 Replies
View Related
Feb 9, 2007
I have an excel file with multiple worksheets that contain data that needs to be collated
There are a # of different product #s on all spreadsheets I need a total qty for each product # by calculating the qty of each style # (ie how many I1 across all sheets?)
Its 1pm now and need to calculate all by 2pm!
View 9 Replies
View Related
Oct 5, 2012
I have to run a report each morning and in once cell I need to pull in the contents of another cell from another worksheet. I'd rather not have to open the additional file each morning to copy and paste special the values so I'd like to add to my current macro to pull this data in automatically. I have to do this for two different reports/portfolios and the data I am pulling in for each portfolio is located in the same exel file but under two different tab names, the portfolio names 2010 and 2045.
Deliverables
The spreadsheet I am pulling data in from is located here:
K:Risk OversightMarket RiskTracking ErrorBARRA
and the file name is: Daily Barra Tracking Error.xls
Tab name would be 2010 for the 2010 portfolio or 2045 for the 2045 portfolio The vlookup will be from "A32:B2500" and I would like to incorpoprate an IFERROR function that returns "" in the event of an error. Column "A" are dates and column "B" is the data I need to pull in.
Receivables I am pulling the data into cell "J23" of a summary report (sheet 2) and would like the vlookup to read something like this:
IFERROR(VLOOKUP("K1"Active.Workbook.Sheets2,[K:Risk OversightMarket RiskTracking ErrorBARRA"&"Daily Barra Tracking Error.xls"(Sheets)]."2010""A32:B2500",2,0),"")
This should return a blank cell if the date cannot be found in the data spreadsheet and return the correct data for teh correct date being referenced in my summary sheet.
how to do the bracketed parts for pulling in the right spreadsheet and tab.
View 9 Replies
View Related
Jul 14, 2009
I'm having trouble to get VLOOKUP to work. Basically I need to look up Management Teir information from Sheet1 onto Main sheet as per the file attached. Both RecordID columns are Text so they should work right? But VLOOKUP returns #NA error.
View 4 Replies
View Related
Feb 23, 2012
I have a vlookup function which takes the data from a table in a closed file. A folder where the file would be located will change based on the value in the cell reference.
I used below formula which works, but every time when i drag or try to edit and enter, the path of the file path brokes and i need to reselect the path where the file is located. Is there a way where i can stop this?
Code:
=VLOOKUP(A2,'C:&E1&[Myfile.xls]Sheet2'!$A:$B,2,0)
View 4 Replies
View Related
Oct 10, 2011
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies
View Related
Aug 18, 2009
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
View 3 Replies
View Related
Oct 5, 2009
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?
View 5 Replies
View Related
Oct 29, 2012
I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
View 4 Replies
View Related
Dec 18, 2012
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
View 1 Replies
View Related
Jul 13, 2009
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies
View Related
Jun 9, 2009
I’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?
The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
View 14 Replies
View Related
Jul 24, 2009
I have data in a pivottable which I want to compare with another table. The lastcolumn+1 of the pivottable needs to get data from another table. I have this for an example:
View 5 Replies
View Related
Sep 17, 2008
I have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.
This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.
I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:
View 14 Replies
View Related
