using text from cell in lookup formula

  • Thread starter Thread starter djames2007
  • Start date Start date
D

djames2007

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

Sheeloo said:
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 said:
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.
 
Back
Top