Formula to calculate sum with file referenced in cell (similar to CONCATENATE)
I want to reference the content of of D13 in the below formula:
SUM('[BRT Weekly Resource Report_3-22-11.xls]BRT Report'!$S$7:$S$25)
Cell D13 current has the text value:
C:\Documents and Settings\g1gq\Desktop\Weekly Resource Report\Source Data\BRT Weekly Resource Report_3-22-11.xls
Cell D13 is populated (value only) as files are imported. I know there is a method to program this reference, I just can't remember it for the life of me.
Thanks in advance
Last edited by ggremel; 04-03-2011 at 09:37 PM.
Are you trying to think of INDIRECT() maybe?
http://www.excelfunctions.net/Excel-...-Function.html
Perhaps, I need help in talking the full file name (currently in D13) and run a sum on the workbook being referenced in D13.Originally Posted by Cutter
Within the same workbook I have SUM('BRT Report'!G7:G3000) to calculate this total. Now I need to somehow reference D13 to pull in C:\Documents and Settings\g1gq\Desktop\Weekly Resource Report\Source Data\BRT Weekly Resource Report_3-22-11.xls into another formula to caculate the total on this workbook.
Try this:
=SUM(INDIRECT("'["&D13&"]BRT Report'!$S$7:$S$25"))
Last edited by Cutter; 03-27-2011 at 05:27 PM.
Correction (had the square bracket in wrong place):
=SUM(INDIRECT("'"&SUBSTITUTE(D13,"\","\[",6)&"]BRT Report'!$S$7:$S$25"))
I am receiving a #ref! file on both formulas. I uploaded two files with the macro and reference cells with sample data.
It looks like we are getting somewhere in this direction. Let me know your thoughts.
Got it. Sum requires the file open. Very good. I just need to generate a sum product and we are rolling.
Thank you!
By the way, how do we mark this feed solved?
Ahhh, very good. I was wondering about that.
Click on the FAQ button at top of page - the instructions are there.
I must be missing something else. If the file is open, it works. If I close it and press F9 it goes to #ref!. Any thoughts?
=SUMPRODUCT(INDIRECT("'"&SUBSTITUTE($D$13,"\","\[",6)&"]BRT Report'!G$7:G$3000"))
INDIRECT doesn't work on closed files. MoreFunc's Indirect.Ext does.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
It turns out that INDIRECT() will only work when the file is open - no workaround.
There is an alternative but you would need the Morefunc Addin which has INDIRECT.ext that can reference a single cell on a closed file.
Here's some info:
http://www.dailydoseofexcel.com/arch...sed-workbooks/
Last edited by Cutter; 03-27-2011 at 07:27 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks