Hi Folks:

I have been scratching my head over this problem for some time.

perhaps someone out there can help:

I want to use VLOOKUP in the following way:

the look up value is in 1st column of one spreadsheet and the table_array
is range A:B of another spreadsheet which is unsaved. How do I reference
the table array when I dont know the name of the workbook?

I know that the unsaved workbook is the last one opened so ive tried

set myworkbook=workbooks(wokrbooks.count)

then in my VLOOKUP formula
=VLOOKUP(A:A,[myworkbook]CA!$A:$B,2)

but that doesnt work

ive also tried

Set myrange = myworkbook.Worksheets("mysheet").Range("$A:$B")


=VLOOKUP(A:A,myrange,2)


and that does not work either

any help would be really appreciated


thanks