Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Specifying File Name In V Lookup

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have 400 excel files with different names and only one worksheet, all the
filenames are of the form "yyyymmdd-closing_prices.xls", where yyymmdd
represent a date, the worksheets have the name "yyyymmdd-closing_prices"
corresponding to the name.

I also have a master Excel file in which I have a vlookup function looking
at these sheets "vlookup(B$1,'XXXXXXXX'!$1:$65000,3,0)" where XXXXXXX refers
to each of the 400files. as you can imagine, the array has to change with
each file name for the 400 entries, I have developed a lookup table in the
master file that automatically gives the file name and array (in text format)
next to any date I enter, but I cannot use it at the moment. How can I get
Vlookup() to use the lookup table in my master file instead of retyping the
filename and range manually.


View Answers     

Similar Excel Tutorials

Vlookup with a Range of Numbers in Excel
How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quanti ...
HLOOKUP in Excel
The Hlookup function allows you to scan a row from left to right in search of a value and then return the contents ...
Find the Next Blank Row with VBA Macros in Excel
Learn how to find the next empty cell in a range in Excel using VBA and Macros.  This method will skip any blanks ...
Make Your Macros Available in All Workbooks in Excel
In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook. To do this we need t ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics

I have a folder in my HDD. The folder name is DC.
this DC folder contains lots (more than 100 files) of xls files. The names of the files are 1dc.xls, 2dc.xls, 3dc.xls.....155dc.xls etc.
Inside of those files there are some data, but I need to work with data in cell only from A2 till N2.

From the other side I have master xls file, called DC Master.xls. In the first column A I enter the name of the hundrets of my files, like column name is "File Name from folder DC" and it will be 1dc.xls, 2dc.xls etc.
But in in master file in second column B I want data from the second column of *dc.xls file.
Let's say in master file in column A3 I enter "3dc.xls" and in master file in column B3 I want the data from file 3dc.xls from it's column A2. The thing is I don't even want to open file 3dc.xls and just put simple reference to it's cell A2. What I want to copy all other/future files 156dc.xls to my folder DC and put in my master fild DC Master.xls in the first column "156.xls" and the data from 156dc.xls file appeared in column from left.

My idea was in master file Master.xls in the second column B get the data from the file which name in the same row but from the left.

Imagine this is Excel cells and I want to see this:

1 |File name|First data from file|
2 |156dc.xls |Alex G |

But in formula I did as follows:

1 |File name|First data from file|
2 |156dc.xls |='C:\Users\Eugene\Desktop\DC\['&'156dc.xls'&]Sheet1'!$A$2|

But it didn't work.
Using combining of the cells value ( I always know that the dc files will be stored in one folder and the name of the files will be very similar) I was able to generate correct URL adddress of the file but as text form not as as link.

My question is - how may I convert text value of the cell to LINK?

Ouch...guess I was specific enough...
Thanks fellows!

Hello all,

Ive been working on this macro for some time now and am close I feel. So I need another set of eyes to help me fix the coding so my desired result is attainable.

I am building a simple macro that uses the vlookup function from rows 2 to last row with string. This function accesses a master file and searches the master file for the string in cell "D"whatever"" and then returns a corresponding string from the Master file to the initial file macro is running on. I have the vlookup able to work on one specified row. But it will not loop. I am not sure what is wrong in my coding that will not allow it to run.

Please help.


Example1 contains the macro. you will see the basic layout I am going for. the "target return" is the place where the corresponding string from the master file will be added.


This is the master file.

Any and all help, please.

Dear All

I am currently working to on piece where I am receiving various files from different sources they are all in same format. I need to compile those together in one master sheet but the master sheet's column's are'nt same as the source file plus the master sheet requires me to pull data from another file which contains individual's business area. I have put togeher a script below which as long as I copy data from first file is fine but when I try to run this script on another file it doesn't work. I tried to use .End(xlDown) but it starts copying data from last row meaning the last row gets overrided by the new set of data.

Sub ComplileData()

Dim FileName As String

FileName = InputBox("Please enter exact file name")

With Workbooks(FileName).Sheets("Cntry_Data")
.Range(.Range("A5"), .Range("A65536").End(xlUp)).Copy Workbooks("Template").Worksheets("Main").Range("A5")
End With

With Workbooks(FileName).Sheets("Cntry_Data")
.Range(.Range("B4"), .Range("B65536").End(xlUp)).Copy Workbooks("Template").Worksheets("Main").Range("B4")
End With

End Sub

Can anyone please help


I'm trying to create a custom function that will basically do a vlookup and return a result from the same table everytime, but that is not included in the workbook.

For example today someone sends me a file, in column B is a product code, I open up a workbook that contains a master list of product codes and prices, and use the vlookup function in the file I received to lookup the price from my workbook that contains the prices.

in received workbook cell B6 I type:
=VLOOKUP(B5,'[Master File.xls]Sheet1'!$K$2:$L$19751,2,FALSE)

I'm looking for a way to store the lookup table that is currently in [Master File.xls]Sheet1'!$K$2:$L$19751 somehow in VBA so that I could write a custom function such as:


that would return the lookup value from the second column of my prices table, but I don't want to have include the table in the workbook that I'm working on. Is there a way to store a worksheet associated with a custom function in the personal macro workbook, or as part of an add-in ? Or any other way that anyone knows of to do something like this?

If anyone can get me pointed in the right direction on this, it would be very much appreciated.


I have a "cat_no" file and a "master inventory" file.
I need to do a vlookup to match the catalog #'s and mfr name in the cat_no file and the master inventory file. I also need to pull the pricing and packing string from the master inventory file and add it to the cat_no file where item numbers match.

Could someone please help on the vlookup formula?

Please let me know if I did not provide enough info

Hi everyone. I am new to this forum so hopefully have got this post correct!

I have created a spread sheet that imports a CSV file of data and then I create a number of summary reports from this raw data in other workbook sheets.

The trouble I have is that the Vlookup and Hlookup functions seem to need a fixed column or row identifier to return a given value. This means the CSV file has to be set-up very precisely with the different columns downloaded from the master database in the same order every time. Unfortunately as I expand this tool and add new data to the CSV file, the order of columns changes and I need to rebuild the whole worksheet.

Is there a way to use a column heading instead of column number to complete the lookup function. For example lookup cell containing "Mark" from the column heading "Names" and return the result from the column heading "Age". At the moment I can only lookup "Mark" in column 1 and return the value from column 3.

Similarly I have recorded a macro to sort data automatically, but the macro also only picks columns by number. For example
Selection.AutoFilter Field:=5, Criteria1:="<>"

Is there a way that field 5 can be identified by the data title rather than position in the spread sheet? Ie it does not matter if the "Age" column is the 5th or 4th in the CSV file.

Thanks for your help with this...

I work at a property managmement company and am creating a workbook to track apartment renovations. I have created 13 spserate worksheets in the workbook. I have a master for the properties and their management team to refer to and 12 monthly sheets. I am attempting to have the managers enter the data and then would like to use an "IF" function to send all of Februaries respevtive entries to the February worksheet. I am atta/ching a file with a blank January and a master worksheet. How is a range of dates set in an "IF" function? I have a attached the file and would appreciate any input

Example) Here are just a couple of attempted variations of the function to get info from worksheet, but it is not working for me. They are located on the January worksheet in cells B4 thru B10.



Good day,

I hope that I am not asking too much with this one.

I have a workbook with 3 worksheets, 2 containing data and 1 used as a search and display sheet. I enter information into a cell, press enter and the relevant data is displayed in various cells.

I would like to create a userform to do the job of the search/display sheet, this is mainly to make it look more professional!

What do I need to add to the userform? Textboxes, Labels, Command Buttons etc, etc.

How would I go about transferring/copying the following data into a userform?



A1 B1 C1 D1 E1 P1 =TODAY() =NOW() A3 =COUNT(Master!A4:A301) Enter Product code below P N M E F S W S S G R A5 =VLOOKUP(C5,Master,7,FALSE) =VLOOKUP(C5,Master,8,FALSE) =VLOOKUP(C5,Master,9,FALSE) =VLOOKUP(C5,Master,10,FALSE) =VLOOKUP(C5,Master,11,FALSE) =VLOOKUP(C5,Master,12,FALSE) =VLOOKUP(C5,Master,13,FALSE) =VLOOKUP(C5,Master,14,FALSE) =VLOOKUP(C5,Master,15,FALSE) =VLOOKUP(C5,Master,16,FALSE) =VLOOKUP(C5,Master,17,FALSE) B M O M C M G H L S P A7 Product details =VLOOKUP(C5,Master,18,FALSE) =VLOOKUP(C5,Master,19,FALSE) =VLOOKUP(C5,Master,20,FALSE) =VLOOKUP(C5,Master,21,FALSE) =VLOOKUP(C5,Master,22,FALSE) =VLOOKUP(C5,Master,23,FALSE) =VLOOKUP(C5,Master,24,FALSE) =VLOOKUP(C5,Master,25,FALSE) =VLOOKUP(C5,Master,26,FALSE) =VLOOKUP(C5,Master,27,FALSE) =VLOOKUP(C5,Master,28,FALSE) Des A9 =IF(ISNA(VLOOKUP(C5,Master,2,FALSE)),"Incorrect Product code",VLOOKUP(C5,Master,2,FALSE)) =VLOOKUP(C5,Master,29,FALSE) =VLOOKUP(C5,Master,30,FALSE) =VLOOKUP(C5,Master,31,FALSE) =VLOOKUP(C5,Master,32,FALSE) =VLOOKUP(C5,Master,33,FALSE) =VLOOKUP(C5,Master,34,FALSE) =VLOOKUP(C5,Master,35,FALSE) =VLOOKUP(C5,Master,36,FALSE) =VLOOKUP(C5,Master,37,FALSE) =VLOOKUP(C5,Master,38,FALSE) AL =VLOOKUP(C5,Master,39,FALSE) =VLOOKUP(C5,Master,40,FALSE) =VLOOKUP(C5,Master,41,FALSE) =VLOOKUP(C5,Master,42,FALSE) =VLOOKUP(C5,Master,43,FALSE) =VLOOKUP(C5,Master,44,FALSE) =VLOOKUP(C5,Master,45,FALSE) =VLOOKUP(C5,Master,46,FALSE) =VLOOKUP(C5,Master,47,FALSE) =VLOOKUP(C5,Master,48,FALSE) A11 =VLOOKUP(C5,Master,3,FALSE) =VLOOKUP(C5,Master,49,FALSE) =VLOOKUP(C5,Master,50,FALSE) =VLOOKUP(C5,Master,51,FALSE) =VLOOKUP(C5,Master,52,FALSE) =VLOOKUP(C5,Master,53,FALSE) =VLOOKUP(C5,Master,54,FALSE) =VLOOKUP(C5,Master,55,FALSE) =VLOOKUP(C5,Master,56,FALSE) =VLOOKUP(C5,Master,57,FALSE) =VLOOKUP(C5,Master,58,FALSE) Wa =VLOOKUP(C5,Master,59,FALSE) =VLOOKUP(C5,Master,60,FALSE) =VLOOKUP(C5,Master,61,FALSE) =VLOOKUP(C5,Master,62,FALSE) =VLOOKUP(C5,Master,63,FALSE) =VLOOKUP(C5,Master,64,FALSE) =VLOOKUP(C5,Master,65,FALSE) =VLOOKUP(C5,Master,66,FALSE) =VLOOKUP(C5,Master,67,FALSE) =VLOOKUP(C5,Master,68,FALSE) A13 =VLOOKUP(C5,Master,4,FALSE) =VLOOKUP(C5,Master,69,FALSE) =VLOOKUP(C5,Master,70,FALSE) =VLOOKUP(C5,Master,71,FALSE) =VLOOKUP(C5,Master,72,FALSE) =VLOOKUP(C5,Master,73,FALSE) =VLOOKUP(C5,Master,74,FALSE) =VLOOKUP(C5,Master,75,FALSE) =VLOOKUP(C5,Master,76,FALSE) =VLOOKUP(C5,Master,77,FALSE) =VLOOKUP(C5,Master,78,FALSE) Ar =VLOOKUP(C5,Master,79,FALSE) =VLOOKUP(C5,Master,80,FALSE) =VLOOKUP(C5,Master,81,FALSE) =VLOOKUP(C5,Master,82,FALSE) =VLOOKUP(C5,Master,83,FALSE) =VLOOKUP(C5,Master,84,FALSE) =VLOOKUP(C5,Master,85,FALSE) =VLOOKUP(C5,Master,86,FALSE) =VLOOKUP(C5,Master,87,FALSE) =VLOOKUP(C5,Master,88,FALSE) A15 =VLOOKUP(C5,Master,5,FALSE) =VLOOKUP(C5,Master,89,FALSE) =VLOOKUP(C5,Master,90,FALSE) =VLOOKUP(C5,Master,91,FALSE) =VLOOKUP(C5,Master,92,FALSE) =VLOOKUP(C5,Master,93,FALSE) =VLOOKUP(C5,Master,94,FALSE) =VLOOKUP(C5,Master,95,FALSE) A17 =COUNTA(Ingredients!A4:A200) =COUNTIF(Master!C4:C302,"Yes") Enter Part code below =(C19) Comprises A19 =VLOOKUP(C19,ing,3,FALSE) =VLOOKUP(C19,ing,4,FALSE) =VLOOKUP(C19,ing,5,FALSE) =VLOOKUP(C19,ing,6,FALSE) =VLOOKUP(C19,ing,7,FALSE) Des =VLOOKUP(C19,ing,8,FALSE) =VLOOKUP(C19,ing,9,FALSE) =VLOOKUP(C19,ing,10,FALSE) =VLOOKUP(C19,ing,11,FALSE) =VLOOKUP(C19,ing,12,FALSE) A21 =IF(ISNA(VLOOKUP(C19,ing,2,FALSE)),"Incorrect Part code",VLOOKUP(C19,ing,2,FALSE)) =VLOOKUP(C19,ing,13,FALSE) =VLOOKUP(C19,ing,14,FALSE) =VLOOKUP(C19,ing,15,FALSE) =VLOOKUP(C19,ing,16,FALSE) =VLOOKUP(C19,ing,17,FALSE) A23 E23 P23

I have around 700 workbooks in a folder on my PC.

My master file(master database.xlsx) is kept open from which i have to Vlookup the values into these 700 workbooks.

The "agent Id" column is common in both the master database.xlsx as well as those other workbooks also.

The thing i want to do is i need the data from the "reporting supervisor" which is in the master database.xlsx file to be available in the G column of the other 700 books ( i have attached only 4 sample books). The G column can be then named as "Reporting Supervisor". Since "Agent ID" is available in both files we can vlookup taking this as a base. I have attached the 4 sample files out of 700 as well as the master database file in a zip file.

Hope my question is clear to you. Please give a nice solution to this or else i have to open all the 700 files and do vlookup one by one.

Hello All,

I have 22 separate files that I am attempting to pull information from using vlookup (back to a master file). All files are stored on a shared drive and each file is accessed by a different user. I am attempting to direct vlookup to a specific file (i.e. doe.xls) based upon the user name in the master file. If user = John Doe lookup data in doe.xls. Therefore, instead of sifting through 22 files it would go directly to the source file.

I am familiar with vlookup, but I am not sure if this particular command is possible.

Any assistance would be greatly appreciated.

Thanks in advance!


Hello All,

I have 22 separate files that I am attempting to pull information from using vlookup (back to a master file). All files are stored on a shared drive and each file is accessed by a different user. I am attempting to direct vlookup to a specific file (i.e. doe.xls) based upon the user name in the master file. If user = John Doe lookup data in doe.xls. Therefore, instead of sifting through 22 files it would go directly to the source file.

I am familiar with vlookup, but I am not sure if this particular command is possible.

Any assistance would be greatly appreciated.

Thanks in advance!


In Excel2000, I have the following function:

Function WC_Name(ByVal WC)

WC_Name = Application.WorksheetFunction.VLookup(WC,
Workbooks("master.xls").Worksheets("fall06").Range("b4:n500"), 13,

End Function

The problem is that it does not work if the "master" file is not open.
How can I have the function open the file if it isn't already?



My main problem is the nesting levels of my statement; I keep getting a compatibility error when I save my file due to the above function. It actually only has 7 levels, unless I am way off on how the nesting works.

Is there another option or formula that will serve the same purpose?
I have tried to replace some of it with a range of sheets but must be doing something wrong.

I need a formula re-write or something!

Also, this has been entered as an array in my sheet.


What I what to do in combed 5 lookup table in one statemnet can this be done.





Thank you all for your help,
I need your help once again for consolidating various files into my master file , I found similar codes from site but my requirement is bit different from those.

I have a master file in my C:/Master folder with Heading in row 2( Same as all other files)
and I have some 100 files in C:/working_Files folder( Heading in 301th row and datas from there on)

I need a macro in my master file to consolidate the data from all those 100 files ( starts from 302 row in all the files) and put in my master file from row 3 onwards one afterthe other)
however this should happen based on a condition
If I enter the Date in Cell A1 in my master file( Say 05-Dec-2010) Then look in Column AC(Processed Date) in all those 100 files, filter the data by the above date and get the data to my master file
Next time if I change the date and run again, then get data only for this date and paste below. But by mistake if I keep the same date and run the macro then it should tell me that "I am trying to import duplicate data"

Cross post :

I have the following code in a macro and when it is run I get a Run Time Error 438 Object doesn't support this property or method. This occurs at the first occurrance of the destination/source. Any suggestions?


Sub TestDatabase()
' TestDatabase Macro
' Macro recorded 11/12/2008 by JLZ
' Keyboard Shortcut: Ctrl+t
' syntax is [destination].Value = [source].Value         
    Windows("File 2.xls").Range("B3").Value = Windows("File 1.xls").Range("E2").Value
    Windows("File 2.xls").Range("C3").Value = Windows("File 1.xls").Range("E3").Value
    Windows("File 2.xls").Range("D3").Value = Windows("File 1.xls").Range("E4").Value
    Windows("File 2.xls").Range("E3").Value = Windows("File 1.xls").Range("C6").Value
    Windows("File 2.xls").Range("F3").Value = Windows("File 1.xls").Range("C7").Value
    Windows("File 2.xls").Range("G3").Value = Windows("File 1.xls").Range("C8").Value
    Windows("File 2.xls").Range("H3").Value = Windows("File 1.xls").Range("E8").Value
    Windows("File 2.xls").Range("I3").Value = Windows("File 1.xls").Range("E9").Value
    Windows("File 2.xls").Range("J3").Value = Windows("File 1.xls").Range("E28").Value
    Windows("File 2.xls").Range("K3").Value = Windows("File 1.xls").Range("E29").Value
    Windows("File 2.xls").Range("L3").Value = Windows("File 1.xls").Range("E30").Value
    Windows("File 2.xls").Range("M3").Value = Windows("File 1.xls").Range("E31").Value
    Windows("File 2.xls").Range("N3").Value = Windows("File 1.xls").Range("E33").Value
    Windows("File 2.xls").Range("O3").Value = Windows("File 1.xls").Range("B29").Value
    Windows("File 2.xls").Range("P3").Value = Windows("File 1.xls").Range("B31").Value
    Windows("File 2.xls").Range("Q3").Value = Windows("File 1.xls").Range("B33").Value
    Windows("File 2.xls").Range("R3:V3").Value = Windows("File 1.xls").Range("A12:E12").Value
    Windows("File 2.xls").Range("W3:AA3").Value = Windows("File 1.xls").Range("A13:E13").Value
    Windows("File 2.xls").Range("AB3:AF3").Value = Windows("File 1.xls").Range("A14:E14").Value
    Windows("File 2.xls").Range("AG3:AK3").Value = Windows("File 1.xls").Range("A15:E15").Value
    Windows("File 2.xls").Range("AL3:AP3").Value = Windows("File 1.xls").Range("A16:E16").Value
    Windows("File 2.xls").Range("AQ3:AU3").Value = Windows("File 1.xls").Range("A17:E17").Value
    Windows("File 2.xls").Range("AV3:AZ3").Value = Windows("File 1.xls").Range("A18:E18").Value
    Windows("File 2.xls").Range("BA3:BE3").Value = Windows("File 1.xls").Range("A19:E19").Value
    Windows("File 2.xls").Range("BF3:BJ3").Value = Windows("File 1.xls").Range("A20:E20").Value
    Windows("File 2.xls").Range("BK3:BO3").Value = Windows("File 1.xls").Range("A21:E21").Value
    Windows("File 2.xls").Range("BP3:BT3").Value = Windows("File 1.xls").Range("A22:E22").Value
    Windows("File 2.xls").Range("BU3:BY3").Value = Windows("File 1.xls").Range("A23:E23").Value
    Windows("File 2.xls").Range("BZ3:CD3").Value = Windows("File 1.xls").Range("A24:E24").Value
    Windows("File 2.xls").Range("CE3:CI3").Value = Windows("File 1.xls").Range("A25:E25").Value
    Windows("File 2.xls").Range("CJ3:CN3").Value = Windows("File 1.xls").Range("A26:E26").Value
    Windows("File 2.xls").Range("CO3:CS3").Value = Windows("File 1.xls").Range("A27:E27").Value
    Windows("File 2.xls").Range("B3").Value = Windows("File 1.xls").Range("E2").Value

    Windows("File 2.xls").Activate
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    Windows("File 1.xls").Activate

End Sub

How can I used the vlookup function if the "lookup value" I'm trying to find in a range of data contains quotes, ex.:
Fluorescent, (1) 18" T12 lamp

Let's say that lookup value is in cell O10, my table array is in $A$!:$H$!100, the column index is 7; I tried this formula first:
and I get the #N/A error.

I was going to use the substitute function to replace the " with a ? (single character wildcard), so I tried:
=VLOOKUP(SUBSTITUTE(O16, """, "?"),$A$1:$H$140,7,FALSE)
but that won't work either.

Any suggestions, please? Much appreciated.

Hi all,

Sorry for my first post to be asking for help. I don't have any experience with macros, but I'm trying to create one which will allow me to copy rows from one workbook to another based on whether a row with a unique ID already exists in the destination file.

I have two files, a master file called "master transfer file.xls" and then will get report files called "new data.xls." Both files have the same columns (but the master file has some additional manually entered columns at the far right. The new data files will have some records that are already in the master file and I want to ignore those (if column E in the row in the new data file equals any row column E in the master file. If the the value for column E doesn't already exist in the master file, I want to copy that row.

Can anyone provide a suggestion or point me in the right direction?



I have created a macro that combines all tab delimited text files within a specified folder into one master file (Excel). I would like to name this file "Master" & "doff" where "doff" is the date that the first file to be imported was created or last modified as viewed by the operating system. Is this possible in Excel VBA? Thanks!

Hi everyone,

I'm trying to create a formula that will return values from another workbook and that formula would use a text string for the file location. I'm not sure if I'm explaining it right, so it's probably best to describe what I'm hoping to achieve.

I'm working on a purchase order for roughly 200 transmitters for my engineering company on behalf of our client. Even though there's 200 transmitters, the nature of the project means that in reality there are only 8 "groups" of transmitters, with each group having the exact same information and requirements. So, I had this idea of creating 8 "master files" which I can change and in doing so, change the information of every data sheet associated to that master file. This lets me vastly shorten the amount of time needed to change these datasheets.

That part I got working by using simple references like ='(file location) '!$A$1 for instance.

It dawned on me that everytime the datasheets would go through a revision, the user would have to go into every single data sheet and change the location or name of the "master file" in order to properly link these new revised data sheets with the correct new, revised master file.

So I got the idea of using the data sheet file names as a way to link the proper data sheet revision to the proper master file revision. So if the original files were Datasheet Rev. A and Master Rev. A and now we moved onto revision B, then Datasheet Rev. B would link automatically to Master Rev. B.

What I've got so far is using =CELL("filename",A1) in my test data sheet to gets the data sheets filename, using =RIGHT(cell with the filename, 3) to extract the revision number, then using concatenate to create the location of the revised master file.


=CELL("filename",A1) returns N:\...\[test data sheet Rev.B.xls] in cell W5
=RIGHT(W5, 5) returns B.xls] in cell W6

Cell W14 = N:\...\[test master file Rev.

then concatenate to create the desired file location

=CONCATENATE (W14, W6) returns N:\...\[test master file Rev. B.xls] in cell W15

and this is where I get stuck. I want to use this new location of the master file in the reference cells on the data sheet. I tried doing ='W15 '!$A$1 but that did nothing. I also named W15 "Master" and used ='Master '!$A$1 but doesn't work either. I have a feeling the answer isn't that complicated, but I'm stumped.

Please help!

I have two workbooks. One is a no-nonsense form interface that my bosses will use to enter safety information. I'll call this workbook "Form". This file is stored locally on each of their computers. The other workbook is stored on a common drive. I'll call it "Master".

When my bosses fill out the Form and click "Submit", the Master file is opened, and certain cells are populated based on information entered in the Form.

This is the code I am using to make this happen:


    Workbooks.Open FileName:="V:\Field Check-Ins\MASTER.xls"
    Sheets("Check Ins").Activate

This opens the Master and activates the appropriate sheet, but I want it to do mo

What I want it to do is check to see if Master is open first. If it is, I want it to go ahead and activate the appropriate sheet. If Master is not open, then I want it to open Master.

Is this possible? Thanks in advance!!


I have a number of xlsx files that I need to compile in one master excel file, they are contained in a master folder and sub folders, all files begin with PJ anything other than this are to be ignored

I have included a few sample files and a master file which shows where the data is from.

I need to search the folders for the files and retrieve the data from the relevant xlsx files and compile them in one master file.

Master file From cell in PJ .xlsx file
Date ------------- E5 ----------------- To cell A1 in Master file
Name ------------ H5 ----------------- To cell A2 in Master file
Time ------------- E8 ----------------- To cell A3 in Master file
Age -------------- H8 ----------------- To cell A4 in Master file
Gender ----------- H11 ---------------- To cell A5 in Master file
Type ------------- E11 ---------------- To cell A6 in Master file
Address 1 -------- E14 ---------------- To cell A7 in Master file
Address 2 -------- E15 ---------------- To cell A8 in Master file
Address 3 -------- E16 ---------------- To cell A9 in Master file
Address ---------- E17 ---------------- To cell A10 in Master file

I have data in a master worksheet. I need to perform a vlookup on a link field to 12 extraneous files. All the extraneous files have the same format but the vlookup statement needs to reference the correct file in order to find the data I need returned. There is an identifier field in the master worksheet that indicates which extraneous file must be referenced for the vlookup to find the link field and return the values needed.

I tried embedding a vlookup statement inside another to acquire the correct path to the extraneous file. But it doesn't work. Nor does the VB version of the same.


If I could get the vlookup statement to change when the identifier field indicates a different extraneous file in which to perform the lookup I would be golden. I can't get the extraneous file reference to change. It is as if it can only look in 1 extraneous file at a time.

Help!? I'm pretty sure I'll need a macro but I'm not a VB guy.


I have a Macro that prompts the user to open a txt file using the row settings and opens it. At the moment the macro imports the txt file into "Book1".

Nows theres two questions;

1. How can I get the Macro to open the txt file into a work book of my choosing, ie, GarrySpreadsheet.xls (Sheet1)?

This is the relevent code;

Workbooks.OpenText Filename:=myFileName, Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0,1), Array(30, 1), Array(43, 1), Array(55, 1), Array(68, 1), Array(82, 1), Array(94, 1), Array(107, 1), Array(120, 1))

2, Before the txt file is placed into my spreadsheet, how can I ensure that the data is "Paste Special, Values" if you know what I mean.

At the moment the Excel is converting the contents of a cell "-Global Business"
to "=-Global Business" and treating it as a formula and a result the remainder of my Macro is not working as it should.

The aim of the above is so I can format GarrySpreadsheet, Sheet1 to "Text" before the txt file gets opened, so "-Global Business" remains unchanged.


I want to use vlookup to convert grades (A, B, C, etc) to numeric values but
have been unable to do so. I've been to the Contextures web page which
gives help on this and have read, in confusion, a solution but am still
unable to get it to work.

This is what I've done:
Sheet 1 I've called "Numeric"

and I've entered a series of text grades in column A

I've created a table of grades and numbers in sheet "Lookup" which is
entered in cells A1:B6 with cells A2:B6 being the field array
Grade Score
E 35
D 45
C 55
B 65
A 75

In Sheet "Numeric" column B adjacent to the text grades (A1:A5) I've
entered the formulas


which, bizarrely, gives the result:

D 75.00
C 75.00
A 75.00
B 65.00
E 75.00

Why is this happening?