Linking to another file using file reference typed cell

  • Thread starter Thread starter Steven
  • Start date Start date
S

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
 
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
Click to expand...

Hi Steven,

The INDIRECT function should do what you are after.

Cheers,
Ivan.
 
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#.

Ivyleaf said:
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
Click to expand...

Hi Steven,

The INDIRECT function should do what you are after.

Cheers,
Ivan.
Click to expand...
 
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#.



Ivyleaf said:
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
Click to expand...
Click to expand...
Hi Steven,
Click to expand...
The INDIRECT function should do what you are after.
Click to expand...
Cheers,
Ivan.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
 
Back
Top