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.