Use filename in cell as a string in a Range filename reference

[6volt]

Sounds complicated but I want to use this cell formula which references another file, ^rut.csv.

=VLOOKUP(RC3,'^rut.csv'!R1C1:R6163C7,2,FALSE)

Cell A1 has a value of ^rut

I've tried replacing the '^rut in the formula with

1) A1 & '
2) 'A1' & '

Doesn't work.

I've tried replacing the entire ^rut.csv to no avail.

But then even '^rut' & '.csv' will not work either which indicates its not just a simple string being used in VLOOLUP.

My guess is that the "string" in the formula is being passed as a parameter to another procedure where maybe ' is not recognized and " must be used.

Any suggestions?
Thanks in acvance,
Tom

 

[SkipVought]

This is spreadsheet related not VBA

use the INDIRECT() function to return a reference from a string

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[6volt]

Yes, I goofed and posted this under VBA.

However, I have discovered the INDIRECT method and it works when I put the entire RANGE in a cell, A1, and then do an INDIRECT (A1).

However, I tried for a couple hours trying to use that method where I only put the filename in A1, leaving the filetype (or extension), the exclamation, and the cell range typed in the VLOOKUP formula, to no avail.

I saw examples where they actually put something like A1&"B2:d20" in the INDIRECT, however, I couldn't make that work.

One example had the filename in one variable, and the range in another for an INDIRECT (cellname1 & cellname2).

I tried that one for about 1 hour and couldn't get it to work - of course, stuff in the exmaple itself was poorly explained. And what happened to the "!" anyway?

Thanks for your help.

Should I move this thread to Spreadsheet I wonder?

Thanks
Tom

 

[SkipVought]

if your reference is ^rut.csv'!R1C1:R6163C7 and A1 contains ^rut then
[tt]
INDIRECT("'"&A1&".csv'!R1C1:R6163C7")
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]