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

On Apr 5, 4:03*am, Steven wrote:
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.

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" wrote:

On Apr 5, 4:03 am, Steven wrote:
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.

On Apr 5, 5:35*am, Steven wrote:
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" wrote:
On Apr 5, 4:03 am, Steven wrote:
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 he

http://www.dailydoseofexcel.com/arch...sed-workbooks/

for further info / potential workarounds.

That'll teach me for posting without trying it :)

Cheers,
Ivan.