Linking to an external variable file name

  • Thread starter Thread starter Kelly
  • Start date Start date
K

Kelly

Hi group, I am using the Office 2000 package.

I am trying to find out if there is a way (without using
VB at this point) to create a formula that will allow the
use of a variable file name as an external reference.

Example... If I want cell A1 to contain data from C1 of an
external workbook 1 if cell b1=1 and if b1<>1 then I want
it to get data from workbook 2.

I tried several things including this but so far I can not
get Excel to allow a variable as a file name link?...

This is one of the things I tried....

='[workbook&"variable"&.xls]Sheet1'!C1

Where variable is the cell name that contains either a 1
or a 2.

It appears everything between the [ and ] it is taking
literally??
 
Hi Kelly
ou can use INDIRECT for this
=INDIRECT("'[workbook" & cell_reference & ".xls]Sheet1'!C1")
note the multiple apostrophes: " ' [

This will work only, if the referenced workbook is open. If
it is closed see
http://tinyurl.com/2c62u
for 4 alternative solutions

Frank
 
-----Original Message-----
Hi Kelly
ou can use INDIRECT for this
=INDIRECT("'[workbook" & cell_reference & ".xls]Sheet1'! C1")
note the multiple apostrophes: " ' [

This will work only, if the referenced workbook is open. If
it is closed see
http://tinyurl.com/2c62u
for 4 alternative solutions

Frank

Hi group, I am using the Office 2000 package.

I am trying to find out if there is a way (without using
VB at this point) to create a formula that will allow the
use of a variable file name as an external reference.

Example... If I want cell A1 to contain data from C1 of an
external workbook 1 if cell b1=1 and if b1<>1 then I want
it to get data from workbook 2.

I tried several things including this but so far I can not
get Excel to allow a variable as a file name link?...

This is one of the things I tried....

='[workbook&"variable"&.xls]Sheet1'!C1

Where variable is the cell name that contains either a 1
or a 2.

It appears everything between the [ and ] it is taking
literally??
Click to expand...


.
Thanks Frank....for BOTH of your replies! Yes the file is
Click to expand...
closed I will have a look at some of the info you directed
me to.

Kelly
 
Back
Top