Linking to another file using file reference typed cell
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 |
Linking to another file using file reference typed cell
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. |
Linking to another file using file reference typed cell
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. |
Linking to another file using file reference typed cell
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. |
All times are GMT +1. The time now is 01:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com