Excel INDIRECT VLOOKUP in another file with Variable File Name

[6volt]

I have 2 XL files: one with Data (call it A), another that uses it (call it B).

File A is named ^rut.csv and has one sheet named ^rut. It is open when B is opened.

A is a table of data where DATE in the first column is LOOKed UP.

The following works:

In B, Cell R3C4 defines the A filename and range where the VLOOKUP is going to use:

Cell R3C4:

'^rut.csv'!$A$1:$G$6163

In B, Column 3 is the date which is going to be looked up in A where Date is in Column 1.

The following cell contents works just fine:

VLOOKUP(RC3,INDIRECT(R3C4),2,FALSE)

It matches the Date in A and retrieves the data in Column 2.

The problem is that I want to make the A reference variable rather than fixed to 6163 records.

Further, I want R3C4: '^rut' only which is the "meaningful" portion of the original R3C4. (^rut is a stock symbol so there is nothing more clear than just having the stock symbol in the cell.)

First, I tried to get rid of the 6163 using COUNT()but failed. Trying so many combinations of ' ' and " " and '^rut.csv' OR '[^rut.csv]^rut'

While the following does work:

COUNT('^rut.csv'!C1)

The following do not work:

VLOOKUP(RC3,INDIRECT("'[^rut.csv]^rut'!$A$1:$A"&COUNT('^rut.csv'!C1)),2,FALSE)
VLOOKUP(RC3,INDIRECT("'[^rut.csv]^rut'!$A$1:$A"&COUNT('^rut.csv'!C1)),2,FALSE)
VLOOKUP(RC3,INDIRECT("'[^rut.csv]^rut'!$A$1:$A"&"COUNT('^rut.csv'!C1)"),2,FALSE)
VLOOKUP(RC3,INDIRECT("'[^rut.csv]^rut'!$A$1:$A"&COUNT('[^rut.csv]^rut'!C1)),2,FALSE)
VLOOKUP(RC3,INDIRECT("'[^rut.csv]^rut'!$A$1:$A"&"COUNT('[^rut.csv]^rut'!C1)"),2,FALSE)

The farthest I got was the following at the cost of a lot of time: (where the named cell test1 contains ^rut )

VLOOKUP(RC3,INDIRECT("'["&test1&".csv]"&test1&"'!$A$1:$G$6163"),2,FALSE)

So basically I'm half there as far as the simple ^rut in the cell, however, I have not been able to figure out how to do the COUNT().

Any help would be truly appreciated!

Thanks in advance!
Tom

 

[6volt]

Stumbled onto one that works:

VLOOKUP(RC3,INDIRECT("'["&test1&".csv]"&test1&"'!$A$1:$G$"&COUNTA('^rut.csv'!C1)),2,FALSE)

What was different...

Thanks
Tom

 

[6volt]

It was the "G" that worked. VLOOKUP of a vector does not work. I was jumping the gun because I had subsequent need to do a MATCH on the Date Column A.

Oh well, at least I got it.

Sorry for the post since can't delete.
Tom