Linking to an external variable file name

[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??

 

[Frank Kabel]

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

 

[Kelly]

-----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??

.
Thanks Frank....for BOTH of your replies! Yes the file is

closed I will have a look at some of the info you directed
me to.

Kelly