Using Variable Filename In VLOOKUP

Mar 26, 2014

I'm working on a project where I need to populate a column with vlookups taken from different workbooks, I have for the most part got it working. However currently, everytime it populates a cell it prompts the user to select the sheet from the workbook as there are two. The sheet name is always the same so I tried to add it into the code so it would avoid having to repeatedly click okay while it populated the table.

This is the original code where it asks for the user to select the sheet every time.

[Code].....

This is what is produced when it is run and the user selects the worksheet each time (which works perfectly fine):

[Code] ...........

I tried to enter the Full Costs sheet name into the code like so:

[Code] ......

However this produces the following:

[Code] .....

The issue I have found is that the square brackets that are around the filename are generated automatically, they aren’t in the actual filename and I haven’t put them in, so I am struggling to work out how to add the sheet name in where it wont be included within the square brackets, as that is what's breaking the lookup.

View 3 Replies


ADVERTISEMENT

Variable In Filename Reference

Apr 17, 2007

I'm working on a spreadsheet that will access two weeks worth of data at a time. Is it possible to have the filename reference contain a variable or a link to another cell in it?

For example: ...

View 6 Replies View Related

Split String Variable And Use Some In Filename

Mar 16, 2009

I have a filname called ex: "Statusreport xxyyzz.xls"

I'm only interested to put xxyyzz in a variable and reuse it for saving another workbook.

How can I use the different string functions for this. The word "Statusreport" is used for apporx 20 different workbooks.

View 9 Replies View Related

Pass Filename To String Variable

Aug 3, 2006

Have a file lets say is named

sample.xls

Does anyone know how to retrieve the name of the file in VB?

just the name and put it in a string?

View 3 Replies View Related

Define Path & Filename As Variable

Feb 16, 2007

Private Sub cmdImport_Click()
Dim exportFile As String
exportFile = "c:jpmimportTrinity_ImpImport_" & Format( Date, "dd-mm-yy") + "_" + Format(Time, "hh:mm:ss") & ".csv"
Open exportFile For Output As #1

I put a watch on exportFile and it's correctly set to: "c:jpmimportTrinity_ImpImport_15-02-07_17:55:01.csv"

However if I try to run this I get "bad file name or numer" when I open for output - the path exists and I can write to it, so must have a fundamentally wrong approach.

View 2 Replies View Related

Using Manual Filename Variable To Import Data

Aug 18, 2006

referencing a variable during a data import. I have searched the forums here and the web for a couple weeks and attempted enclosing the variable in many different symbols.

Nothing I have tried works. I have also read chip pearsons guide but I was still not able to get it to work.

I am trying to provide a way for the user to input the filename and location in an input box and then use that variable to import the data. It is only one file that is needed.

The message box filename is only included to allow me to verify the text input.

Dim Filename As String

Sheets.Add. Name = "All Data"
Filename = InputBox("Enter Filename: ", "Enter Filename Location")

MsgBox Filename
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Filename", Destination:=Range("A1"))
.Name = "SHOAlarmsJune2-9"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False

View 3 Replies View Related

Vba Code Which Saves A Workbook With A Variable As The Filename

Jun 25, 2007

I have a VBA form that the user enters information into and once they click submit on the form the code opens a new workbook and then enters the information from the form into that workbook. What I need is for that code to then save the workbook with a filename from one of the fields entered into the form but I'm not too sure how to do this.

View 3 Replies View Related

Get A Module Wide Variable To Provide The Current Filename To The Various Subs

Mar 10, 2009

I am trying to get a module wide variable to provide the current filename to the various subs.

View 2 Replies View Related

Replace Filename With Variable In "Activecell.FormulaR1C1=" Expression

Feb 27, 2009

Replace filename with variable in "Activecell.FormulaR1C1=" expression
I have recorded a macro which contains several lines of code like this one:

View 2 Replies View Related

Windows ("variable Filename").activate

May 26, 2009

I have created a macro to split out information from one excel workbook into 2 additional workbooks, and format them the way we need. Everything works fine on the filename I originally recorded the macro from, but is giving me a 'subscript out of range' error when trying to run the macro on a file with a different name.

currently, my test file has the 2 following fields that I somehow need to have as variables:

Windows("insmacrotest20090418.xls").Activate

and

Windows("Book1").Activate

I need the filenames and workbooks in the parenthesis to be variable so that the macro can be run on other reports with varying filenames.

View 10 Replies View Related

Dynamic Vlookup &"Filename.xls"

Sep 24, 2008

I would like to dynamically lookup to a file based on a date cell. The file path is of the following format

Drive:Folder1Folder2Folder3yyyymmdatayyyymmFilename.xls

I need an IF statement, an ISERROR and a VLOOKUP to get the data I want.

Essentially though I'm trying to call the first date folder path by simply inserting the following formula in that part of the path: (note F3 contains a date)

year(F3)&IF(len(month(F3))=1,"0"&month(F3),month(F3))

and then I'm also putting that same formula before that Filename and following it with an &"Filename.xls"

View 9 Replies View Related

Vlookup With Variable Name

Dec 29, 2009

I'm trying to do a vlookup inside of a FOR Loop where the lookup value is the name of a sheet, which I've made into a variable. When the next count of the FOR Loop changes, so does the sheet name. The formula gives me an error as the lookup value requires the sheet name to be in quotations in order for it to return a value. I've tried every possible combination without any success. Below, I've inserted the code

View 3 Replies View Related

Set Variable Within VLookup

Jun 24, 2013

Trying to set a current region within a vlookup. Never done this before. Data in "test 1" sheet is somewhat dynamic: rows are variable; columns are set to 12 (A:L). Thought CurrentRegion would do the trick. And maybe it still could but referencing it into my vlookup errors out. is my syntax for the vlookup incorrect.

Set test_data = Sheets("test1").Range("A1").CurrentRegion
Sheets("Test2").Range("T2").Formula = "=VLOOKUP($F2,test_data,4,false)"

View 4 Replies View Related

Name Of Sheet As Variable In VLOOKUP

Apr 29, 2014

I am currently trying to use a variable instead of an absolute name for sheets name in VLOOKUP, but it wont work:

Original formula:
=VLOOKUP($D6,ABG!$B$9:$O$12,3,FALSE)

What I would like:
=VLOOKUP($D6,$A$6!$B$9:$O$12,3,FALSE)
with A6 = ABG

View 3 Replies View Related

Variable Array In VLookup

Jul 25, 2014

Basically have a spreadsheet to track an athletic competition going of for the purposes of a fantasy game (like fantasy football). The scores from each event are being copied and pasted into a data pages and then other pages pull from that for calculations. I'm using rankings (rank.eq equation) on a calculation tab, and then using those rankings on a leader-board tab find placement via the VLookup function. The issue I'm running into is ties, when two people are ranked the same. I've been playing with this:

=IF(ISERR(VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE)),VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE),
VLOOKUP($J16,INDIRECT("Men!A" & LOOKUP(J16,Men!A1:A43)+1):$G$43,7,FALSE))

Where it checks for an error in the Vlookup, if its not an error then it does the VLookup, if it is then if looks up the previous ranking and the VLookup array uses Lookup to find the position of the last rank, increments it by one and starts the new Vlookup there.

View 10 Replies View Related

Add Variable Into VBA For A Vlookup Formula

Apr 7, 2014

Adjust this piece of code:

[Code]....

The lookup is for 00.2014, but this is to fixed. Want to use the same code next year to. So I already defined the variable 'jaar' which the user can choose with a validation. (and next year they set it to 2015).

I thought this code would do it but no luck:

[Code] ....

So what would be the correct way? Been shifting with the " " but its only make more and more mess.

View 2 Replies View Related

Vlookup Concatenated Value As A Variable

May 13, 2009

Effectively I need to concatenate two columns in Sheet1, and then vlookup each concatenated value from Sheet1 in another concatenated column in Sheet2 and return the value from the next column

Now that is quite straight forward, but I am new to coding, and I was wondering if there was a way of doing it that doesn't involve having the concatenated values written to a column as a formula. Ideally what I would like it to do is put the concatenated value of Sheet1 together as a variable (I think thats what you call them) and then look up that variable in Sheet2 without Sheet2 creating a concatenated column too.

I'm of the understanding that this might be slower than writing the columns, but there is a reason for my madness.

Let me know what you think, or if i'm barking. Any suggested reading would also be handy, I don't like to bug you guys for the full code straight out.

View 10 Replies View Related

Sum / VLookup And Variable Column

Aug 13, 2012

I have a huge list of data where in column A there are customers names. Column B i can see products they are renting and column C-Z are uinits that they are renting on a given date. (lets say c is August 1st, D is August 2nd and so on). What I want is that if i a new tab in A1 enter a given date and A2 a given model, I want in A3 a formula which would provide me with Totals of that product on specified date by all customers.

Customer name
Product
Aug 1
Aug 2
Aug 3

A
Ice
2
6
4

[Code] .......

So lets say If I ente August 2 and as product Blow, I would like to know how many products of Blow are rented on that day. In this case 3 .......

View 3 Replies View Related

Vlookup Variable Range

Nov 16, 2007

I am trying to do a vlookup that currently looks as follows:

Range("C2").Formula = "=VLOOKUP(A2,SAP!A$2:AA$42,2,FALSE)"
However, the selection of the table_array (SAP!A$2:AA$42) is a set value. Instead I would like to select all the data that is on sheet SAP. The idea is that the data set will not always be A2:AA42, so I need to make this part variable.

Normally I would use

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
to select the entire data set.

View 9 Replies View Related

Variable Vlookup Ranges

May 8, 2008

I have an Excel File with 2 worksheets.

Sheet1 contains column A as "Product Number", Column B as "Product Family". Rows contain about 20,000 lines of data. A Range has been created to cover both columns, and named "ProdRange"

Sheet2 contains Column A (blank), Column B with "Product Number"

What I am trying to do is start at the top of Sheet 2 "Product Number" (B2 is first cell ref), and use this value to do a Vlookup on Sheet 1, to extract the "Product Family" code on the Offset 1 - and insert it back into Sheet 2 Column A.

I have this below :

For i = 2 To LastRow
Range("A" & i).Value = "=VLookup("B" & i, ProdRange, 2)"
Next i

BUT for some reason it doesn't like the [ "B" & i ] part of the equation.

View 9 Replies View Related

Using A Variable In A Vlookup Macro

Feb 25, 2005

I write quite a few macros that require a vlookup formula. The table array is often dynamic which means I must change my program. I am trying to find a way to create the formula to accomodate the growth of the table array. I can count the number of rows in the table by the counta function, but then when I try to use that variable in the formula it bombs.

View 7 Replies View Related

Variable Sheet Name In Vlookup

May 23, 2007

Im have a vlookup which is specifying a table array from another tab on my workbook i.e. =VLOOKUP(J21,'Norwich CR'!$B:$AZ,6,FALSE)

I have another lookup which is feeding back the names of each worksheet (in this example its "Norwich CR". The range will be the same always $B:$AZ but i want the formula above to vary depending on whether my other look up feeds back "Norwich CR" or "Ipswich CR". how i can get =VLOOKUP(J21,'Ipswich CR'!$B:$AZ,6,FALSE) instead just by reading from another column of lookup data????

View 2 Replies View Related

Vlookup On Different Workbook With Variable Sheet Name?

May 28, 2014

I just started venturing into VBA. I'm trying to pull the data from a different workbook that will have the previous month as the sheet's name. I can't get the dynamic part of the code.

[Code] .....

View 5 Replies View Related

How To Setup Variable Range Name In VLOOKUP

Jan 27, 2012

I need to lookup a value in one of 20 lookup tables. Each table has a range name that is stored in a cell. In the formula below, U79 contains the number I want to lookup and cell P79 has the variable range name. Both formulas give me an N/A error. When I enter the actual range name in the formula, it works. how to use a variable range name?

=INDIRECT(VLOOKUP(U79,P79,2,FALSE))

=VLOOKUP(U79,P79,2,FALSE)

View 3 Replies View Related

Inserting VLookup With Variable Source Name With VBA?

Feb 23, 2012

I'm trying to create a macro that will open automatically generated report outputs and manipulate the data.

The macro will run from it's own workbook (Report Macro.xls), sheets / cells within this workbook are available as 'helpers'.

So far what I have working ok is;

Via a browse dialogs in a userform in Report Macro.xls I select 5 different report outputs. I'm storing the full filename & path in Sheet Data, Cells B1:B6 (B1 being the filename & path to the macro file). In column C I have just the filename

The first file is opened and manipulated as required. The next thing I need to do with it is to open the second file (filename / path in B3) and put a Vlookup in cell 02 of the first file which will look for data in the second (the second file will only ever have a single sheet).

As a formula I'd be using

Code:
=VLOOKUP(A2,'Email 22FEB12.XLS'!$B$7:$C$13120,2,FALSE)
In VBA if I use;

Code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],'Email 22FEB12.XLS'!R7C2:R13120C3,2,FALSE)"
It works great. However, the second filename will change and I need to take it from the macro file, sheet 'Data', cell 'C3'.

How can I use the contents of sheet Data, cell C3 in the VBA code above instead of the filename being hard-coded?

View 1 Replies View Related

Referencing Variable Workbook Tab In VLookup?

Nov 29, 2012

I have a challenge with Vlookups!

I have one workbook called Ratecards, which contains various tabs which are named based on a client code. e.g. ABC02

I then have another workbook which has employees in column B and the client code in C3.

The current Vlookup is =VLOOKUP(B8,'[ratecards.xlsx]ABC02'!$A$5:$N$168,4,false) which works fine for one client.

I want to substitute the ABC02 in the vlookup for cell ref C3, as the contents of C3 is variable and I want it to tell the sheet which ratecard to refer to.

View 3 Replies View Related

VBA To Find Variable Data Or VLOOKUP

Jan 6, 2007

I have a large amount of data Range A1:DA6000 on Sheet2 in the following format:

120200 1885 | 42.43 | 130112 1992 | 49.65

120200 1886 | 45.95 | 130112 1993 | 48.08

120200 1887 | 47.65 | 130112 1994 | 49.77

120200 1888 | 45.97 |

120200 1889 | 48.34 | 130133 1887 | x

120200 1890 | 49.42 | 130133 1888 | 41.6

120200 1891 | 48.85 | 130133 1889 | 45.45[/list]

Cell A1 Sheet1 will be variable and I need to find the same value on Sheet2 in all that data and copy the number in the cell to the right to the clipboard. For example:

If Sheet1 A1 = 120200 1885 I want to find 120200 1885 on Sheet2 and copy 42.43 to the clipboard

Or if it's 130133 1887 I want "x" copied to the clipboard and so on.

I think I want to have a macro "Find" the cell and copy the one next to it, but I don't know the syntax to do that. The macro recorder only takes me part way.

I can't figure out how to make a VLOOKUP formula work, as there's way more than 65,000 lines to make a lookup table out of the data.

View 9 Replies View Related

Vlookup With Variable Column Returned

Oct 20, 2008

Aged Debts & Comments

Comments consists of the following

Customer number, Date, Comment, Date, Comment, Date, Comment, Etc Etc....

Aged Debts is

Customer number, Current Outstanding, 30,60,90,120.. Comments.

I would like to bring back the last comment which would be the furthest right comment in "comments".


Ex. of COMMENTS
A B C D E F G
0001 17/10/08 Called Cust. 20/10/08 Payment Promised
0002 15/10/08 No Answer 17/10/08 No Answer 20/10/08 letter Sent
0005 15/10/08 Payment Promised

Ex of Aged Debt..........

View 9 Replies View Related

Vlookup With Variable Column Index

Apr 21, 2009

VLOOKUP($L4,'[mail.xls] new'!$A$6:$AB$261,6,FALSE)

Which gives me proper result...but the problem i m facing is i have many columns after L4 like M4, N4, O4 and so on....where i have to get the result.

So for that every time i have to paste the above formula and manually change the col_index_num i.e. 6 then 7,8,9 in every column where i use the above vlookup. So is there anyway that it will append the col_num_index by 1....i.e.

View 9 Replies View Related

Vlookup Table Array Variable

Jul 8, 2006

=VLOOKUP($B$54,'R:OperationsPerformance ReportingSales ReportsFTW North - INT2006Daily7 July7-02-2006[Sales TL Base Report.xls]MAXimize Summary'!$A:$BA,E$3,FALSE)

I would like to make date in the table array (07 July7-02-2006) a variable, but keep getting an #NA with my limited experience. From what I can gather the single quotes are causing the problem?

View 9 Replies View Related







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