Reference to external file / shorten its name

[Nachtklavier]

Hi there,

I am using formulas in workbook A to get data from workbook B.
Within the formulas the name of Workbook B as well as the object name are displayed:

=vlookup( [@number], 'LongFileNameWithExtension'!TableName[#Data], ... )

My question is: (how) can the "LongFileName" be replaced with something more short, e.g. a reference in workbook A called "RefData", which contains the long file name?
My goal is to keep the formulas as short and clean as possible.

Thank you for any suggestion.

 

[awoohaw]

Dim ShortName as String
ShortName = TheFilePathAndFileNameString

       =vlookup( [@number], ShortName, ... )

 

[Nachtklavier]

Hi @awoohaw,

Thank you very much for your lightning fast answer.
Is VBA the only way to solve this? I should have mentioned that VBA is not allowed to be used.

Thanks.

 

[awoohaw]

no, i misread your first post, i thought it was vba statement

in a cell (say B4) put in the filepathNameExt of the file
then in your vlookup formula put $B$4 in your argument that had the longfilename. but a file name is not a range.
the 2nd argument of VLOOKUP should be a range of some sort.

 

[Fluff]

Will the other workbook be open?

 

[Nachtklavier]

@Fluff Yes, it is open.

 

[Fluff]

In that case you can use Indirect like

=vlookup( [@number],INDIRECT("'"&B1&"'!TableName[#Data]"), ... )

 

[Nachtklavier]

@Fluff Thank you, this is indeed the preferred solution. Right after @awoohaw's reply I was thinking into that very direction.
Btw: Is there any chance to use something similar with Pivot tables (without VBA)? Or is this always kind of hard-coded?

 

[Fluff]

I know nothing about Pivots so cannot answer that.