dynamic vlookup using variable

I am trying to create a dynamic vlookup that passes a variable as the table_array(2nd parameter). I then try to assign the vlookup to a Range Formula. I get an application-defined or object-defined error

Please Login or Register  to view this content.

hi gammaman, a couple of thoughts:

1. LookUp A:Z columns is pretty tough thing: 1000 000 rows x 26 = 26 000 000 cells! Why not to find data bottom row within the code and make the range for the formula more precise?
2. What is this supposed to mean: '11-01-13'!$A$A

To answer question 2, '11-01-13'!$A$A is the first sheet in the workbook. This vlookup forumula is on sheet 3. So basically I need to lookup something in column A:A from the first sheet (11-01-13) and then find the match in variable fileLookup which points to an outside workbook.

Sheet name: '11-01-13'
Range syntax is incorrect: $A$A

Sorry, I do not understand what you are trying to say.

So it is somewhat working now. The formula is being inserted into the cell, but it is appearing as VLOOKUP('Sheet1'!A:A,file1Lookup,2)

It should have the actual file path in place of file1Lookup.

Please Login or Register  to view this content.

Works Great. Thanks for your help

please mark the thread as Solved if we are done