Linking to another file using file reference typed cell

[Steven]

I have a formula such as:

=COUNTA('C:\Accounting\[5010 Advertising.xls]Sheet1'!$C$209:$C$239)

I was wondering if there is a way for example to put in cell A10:
C:Accounting\[5010 Advertising.xls]Sheet1

and then in cell B10 use some sort of concatenation formula or something to
create the COUNTA function by referencing the value (ie filename) in A10
ie: =CountA(&A10&C209:C239) ... but that is not it and I cant figure it out.

Thank you for your help.

Steven

 

[Ivyleaf]

I have a formula such as:

=COUNTA('C:\Accounting\[5010 Advertising.xls]Sheet1'!$C$209:$C$239)

I was wondering if there is a way for example to put in cell A10:
C:Accounting\[5010 Advertising.xls]Sheet1

and then in cell B10 use some sort of concatenation formula or something to
create the COUNTA function by referencing the value (ie filename) in A10
ie:  =CountA(&A10&C209:C239) ...  but that is not it and I cant figure it out.

Thank you for your help.

Steven

Hi Steven,

The INDIRECT function should do what you are after.

Cheers,
Ivan.

 

[Steven]

Thank you for your response. With INDIRECT does the file have to be open.
It works when the file is open but when I close the files and then open the
file that has the INDIRECT linking back to the other file it has a #REF#.

I have a formula such as:

=COUNTA('C:\Accounting\[5010 Advertising.xls]Sheet1'!$C$209:$C$239)

I was wondering if there is a way for example to put in cell A10:
C:Accounting\[5010 Advertising.xls]Sheet1

and then in cell B10 use some sort of concatenation formula or something to
create the COUNTA function by referencing the value (ie filename) in A10
ie: =CountA(&A10&C209:C239) ... but that is not it and I cant figure it out.

Thank you for your help.

Steven

Hi Steven,

The INDIRECT function should do what you are after.

Cheers,
Ivan.

 

[Ivyleaf]

Thank you for your response.  With INDIRECT does the file have to be open.  
It works when the file is open but when I close the files and then open the
file that has the INDIRECT linking back to the other file it has a #REF#.

I have a formula such as:
=COUNTA('C:\Accounting\[5010 Advertising.xls]Sheet1'!$C$209:$C$239)
I was wondering if there is a way for example to put in cell A10:
C:Accounting\[5010 Advertising.xls]Sheet1
and then in cell B10 use some sort of concatenation formula or something to
create the COUNTA function by referencing the value (ie filename) in A10
ie:  =CountA(&A10&C209:C239) ...  but that is not it and I cant figure it out.
Thank you for your help.
Steven

Hi Steven,

The INDIRECT function should do what you are after.

Cheers,
Ivan.- Hide quoted text -

- Show quoted text -

Hi Steven,

Sorrt, it is not something I have tried before. On investigation
though, Indirect does seem to have a limitation with closed workbooks.
Have a look here:

http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/

for further info / potential workarounds.

That'll teach me for posting without trying it

Cheers,
Ivan.