using text from cell in lookup formula
 

I have created a master sheet using a vlookup to get data from another excel
file but everytime I have to change the formulas to correct the file name to
the corrosponding file.
example: "=VLOOKUP($A2,'[3069.xls]resource plan'!$A$2:$BI$36,10,FALSE)"
I am trying to use text in cell a1 as a variable for file name ("3069.xls")
so I only change it once.

using text from cell in lookup formula
 

In your VLOOKUP formula replace '[3069.xls]resource plan'!$A$2:$BI$36
with
=INDIRECT("'[" & A1 & ".xls]resource plan'!$A$2:$BI$36")

assuming A1 has 3069

Basically construct a string which results in '[3069.xls]resource
plan'!$A$2:$BI$36 and use INDIRECT() around it...

"djames2007" wrote:

I have created a master sheet using a vlookup to get data from another excel
file but everytime I have to change the formulas to correct the file name to
the corrosponding file.
example: "=VLOOKUP($A2,'[3069.xls]resource plan'!$A$2:$BI$36,10,FALSE)"
I am trying to use text in cell a1 as a variable for file name ("3069.xls")
so I only change it once.

using text from cell in lookup formula
 

thank you it works with the following
=VLOOKUP($A2,INDIRECT("'[" & A1 & ".xls]resource plan'!$A$2:$BI$36"),10,FALSE)

"Sheeloo" wrote:

In your VLOOKUP formula replace '[3069.xls]resource plan'!$A$2:$BI$36
with
=INDIRECT("'[" & A1 & ".xls]resource plan'!$A$2:$BI$36")

assuming A1 has 3069

Basically construct a string which results in '[3069.xls]resource
plan'!$A$2:$BI$36 and use INDIRECT() around it...

"djames2007" wrote:

I have created a master sheet using a vlookup to get data from another excel
file but everytime I have to change the formulas to correct the file name to
the corrosponding file.
example: "=VLOOKUP($A2,'[3069.xls]resource plan'!$A$2:$BI$36,10,FALSE)"
I am trying to use text in cell a1 as a variable for file name ("3069.xls")
so I only change it once.