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.