Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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.

Frustrated!!!


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 ...
Best Lookup Formula in Excel - Index and Match
A lookup using INDEX and MATCH is like a VLOOKUP without the restrictions.  Index and Match lookups offer you free ...
Return the Min or Max Value Using a Lookup in Excel - INDEX MATCH
Find the Min or Max value in a range and, based on that, return a value from another range. This is an advanced lo ...
Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...

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:

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


But in formula I did as follows:


A B
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!


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


Hi,

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.

Example:
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:

=CustomFunction(B5)

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.

Thanks,


HI,
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
THanks!




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...

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?

Thanks

Ak

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!

Justin




=IF(ISNA(VLOOKUP(Master!A2,RockSlopes!$AE$4:$AI$253,4,FALSE)),IF(ISNA(VLOOKUP(Master!A2,'2ndEastMains'!$AE$4:$AK$250,4,FALSE)),IF(ISNA(VLOOKUP(Master!A2,'1stEastMains'!$AE$4:$AK$250,4,FALSE)),IF(ISNA(VLOOKUP(Master!A2,'1stSouthMains'!$AE$4:$AK$250,4,FALSE)),IF(ISNA(VLOOKUP(Master!A2,SouthBreakout!$AE$4:$AK$250,4,FALSE)),IF(ISNA(VLOOKUP(Master!A2,WestBreakout!$AE$4:$AK$250,4,FALSE)),IF(ISNA(VLOOKUP(Master!A2,Sump!$AE$4:$AI$250,4,FALSE)),"",VLOOKUP(Master!A2,Sump!$AE$4:$AI$250,4,FALSE)),VLOOKUP(Master!A2,WestBreakout!$AE$4:$AK$250,4,FALSE)),VLOOKUP(Master!A2,SouthBreakout!$AE$4:$AK$250,4,FALSE)),VLOOKUP(Master!A2,'1stSouthMains'!$AE$4:$AK$250,4,FALSE)),VLOOKUP(Master!A2,'1stEastMains'!$AE$4:$AK$250,4,FALSE)),VLOOKUP(Master!A2,'2ndEastMains'!$AE$4:$AK$250,4,FALSE)),VLOOKUP(Master!A2,RockSlopes!$AE$4:$AI$253,4,FALSE))


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.


Thanks
rod642

Hi,

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 http://www.rondebruin.nl/fso.htm 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"

Thanks
Cross post : http://www.vbaexpress.com/forum/showthread.php?t=35262


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?

Code:

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
    Rows("3:3").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    Windows("File 1.xls").Activate
    Range("C9").Select

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:
=VLOOKUP(O10,$A$1:$H$100,7,FALSE)
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?

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.

Ex.)

=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:

Code:

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


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!!




Hi

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 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

=VLOOKUP(A1,Lookup!A2:B6,2)
=VLOOKUP(A2,Lookup!A2:B6,2)
=VLOOKUP(A3,Lookup!A2:B6,2)
=VLOOKUP(A4,Lookup!A2:B6,2)
=VLOOKUP(A5,Lookup!A2:B6,2)

which, bizarrely, gives the result:

D 75.00
C 75.00
A 75.00
B 65.00
E 75.00


Why is this happening?

Pete







Hi

I am absolutely new to VBA and trying to create a macro for work. We get daily files with almost the same headers that needs to be consolidated at the end of every month. The headers on the files are usually the same, except at times an extra column may be added at the beginning.
I need a macro to do the following-
Prompt to select the desired files.
Read the headers and append only the desired header columns from these files to a master file(these are Account, User, Modified By, Version). The headers are present in row A. Also, the master file should select the header from the first file only and hence take only the data (row B) from the second file onwards so that the headers are not repeated in the middle in the master file.
In the master table, add a new column at the end which will be the name of the file.
In the master file generated, filter on the column "Version" and delete all the rows except Version="1.0"
Lastly, there are certain values in the excel files that need to be changed. So I need a replace function to change those values. (Ex- Alex to Abraham).

I am sorry, its alot to ask. But please help me. I am in a real crunch. Been at it for the last two day.

I am very new to excel VBA. I have a unique problem of copying data from multiple files.
Here it goes, I have a master file named "master.xlsm" and in that file I have a worksheet named "MyData". In that worksheet, I have column G that contains the file names upto row 20(last available data row). One example of a file name is Myfile1.xls. Each of these files have several worksheets, but the names of worksheets that needs to be opened (and data copied) are written in column H. So for example Myfile1.xls and its corresponding worksheet to be used (say "saving1") sits side by side in column G and H. All the files are in one folder.
I can write simple macros, but this seems diffcult to me. I need to copy a range of data from the relevant worksheets and consolidate it in a single worksheet in the master file. The data has to be copied below each sets of data from the worksheets in the order of column H.
As I am very new to VBA, I will appreciate, if the code has some comments that helps me understand it.
Thanks very much

I have written a code which opens a text file, puts the text in columns, and formats certain cells, then saves the file as an excel type file. At the end of this code, I want to open a saveas dialog box and automatically put the name of the file in the FileName box. However, when I do this, it puts the file as " "filename.txt" " in the file name box. I want it to just say " filename ".

Here's the code....

FName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If FName <> False Then
Workbooks.OpenText Filename:= _
FName , Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1))
ElseIf FName = Flase Then
End
End If

<Here is where I put all the code for the formatting>

fileSaveName = Application.GetSaveAsFilename( _
InitialFilename:= FName , _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If fileSaveName <> False Then
ActiveWorkbook.Save
ElseIf fileSaveName = False Then
End If

.....


Does anyone know what I'm doing wrong?


Hello,
Trying to do a double lookup, or a conditional lookup. Attached spreadsheet will provide much more clarity. I have multiple data sets within a universal file, each data set can contain the same constituant therefore I would like to lookup/vlookup a value only if the constituant is part of the data set I want to pull from.

In attached sheet: if cells in Col A= "F", then vookup("RDT",(array would be data set "F" (I think)),return value in col 2 (assuming vlookup is in col B), t/f)

Thx!

Oh, 2003, running xp pro x86


hello,
every 2 weeks I receive a file named
cabprecolrecongv(yyyymmdd).csv
example: the most recent file was named cabprecolrecongv20080806.csv
where the last 8 characters of the name before the .csv are the date in YYYYMMDD format.
Also this file will be the only .csv file in a particular subdirectory.

What would be the VBA that would look at that .csv file, take the YYYYMMDD from it and place that value into a cell in a worksheet where I could then use the date in some calcuations? I'm going to use the date is to check a column of dates against the date on the filename to see if the column date is older/same/newer than the file date.
thank in advance,
david


So, I am working on a project and stumped with the VLOOKUP. I understand how it works, but want to know if it can perform something else (kind of a modification to it).

I have a file that includes different mutual fund names. They were extracted from a program. These mutual funds have market values as well. Now, I have another excel file that has manually typed in the market values. I'm wanting to make it where the manual mkt values can be linked to the 1st file with the mutual fund names and mkt values.

Obviously, this is easy enough to do in VLOOKUP. However, my problem is that the mutual fund names are slightly different in each respective file. I cannot change the second files names as they are linked to other pages within that xlsx.

So, my goal is to try and find if you can have a lookup value or something similar to a VLOOKUP where excel can guess what you want it to show.

Ex. File 1 name: Vanguard Small Cap
File 2 name: Vngrd Sm-Cap

=VLOOKUP(Reference the Vngrd Sm-Cap,Table Array,Column Index, FALSE)

with the reference to the vngrd sm-cap. I want it to search for that name in the table array, but if it cannot find the name instead of it returning an error or 0 as a result, it will begin searching for names that are similar. Thus, coming to the conclusion of Vanguard Small Cap which actually would be in the table array.

I don't know if this can be done or what function if there is one to use, but if anybody could let me know if they have done anything similar or another solution that would be greatly appreciated. Remember, I can't change the names of the funds because they are linked to other files.

Thanks.


I found a vba code for vlookup across multiple worksheets, but I can't find one across workbooks.

I am creating a tracking system for teachers to view the behavior points each student earns in his or her other periods.

Math is on one workbook (for the math teacher to enter)

English on one, science on one, social studies on one, sped on one, and specials on the last.

So I am working with 6 workbooks.

Let's say I want to find what the person in cell M6 got in 2nd period.

I need a formula like this: =vlookallworkbooks(M6, "D2:E40", 2, False, "Math 8.xls", "Reading 8.xls", "Science 8.xls.....")

Where the arguments are crieria, table range, col number, range lookup, array of workbooks.

All the workbooks are set up the same way, so period one is the same table range: A2:B40 and period two is in the same table range: D2:E40 etc.


Currently right now, I just set up a master workbook that has regular vlookup functions finding what all students received in math period one, reading period one, etc and then it goes to period two ect. Then on the teacher's last period roster, it has vlookup functions referencing the master workbook. And this work; the only thing is a teacher has to remember to open the master workbook for the changes to upload, so I want to get rid of the master workbook and instead use a VBA code across workbooks.

I have attached the 8th grade folder which shows you all the workbooks I am working with.




I am trying to produce a frequency pf pick table using a customer's product master file and a sales volume file and have got some peculiar results. Where, for example, a product code appears in the sales volume file but not in the product master file, the lookup returns the description for the next nearest product code above it. I hope that makes sense.

So, is there a way to return, say, 'Not in pmf' instead of the erroneous description or perhaps even " " (as a blank)?

Help greatly appreciated please.

heFFers