I use the following formula to consolidate data from one workbook to another. It works even when the source workbook is closed.
'[NF White River 2018 CostIt v1.0.xlsm]SUMMARY'!$E$3
To get this to work I selected a blank cell in my destination workbook, typed =, then selected the appropriate cell in the source workbook, then hit ctrl-shift-enter. I now have linked cells.
Because I have several ‘source’ workbooks to retrieve data from, I would rather copy and paste the filename (including path and sheet info) into my destination workbook and then use that as a reference for the corresponding formulas. Something similar to the INDIRECT function. INDIRECT works but only if the source file is open. I tried concatenate but the result is a string, not a formula.
Example of using concatenate:
[TABLE="width: 160"]
<tbody>[TR]
[TD="width: 160"]C:\Users\spanders\Desktop\CostIt\White River Basin-Cost Est\[NF White River 2018 CostIt v1.0.xlsm]SUMMARY'!$E$3
[/TD]
[/TR]
</tbody>[/TABLE]
I’ve spent hours trying to solve this problem. Any suggestions?
Thx,
'[NF White River 2018 CostIt v1.0.xlsm]SUMMARY'!$E$3
To get this to work I selected a blank cell in my destination workbook, typed =, then selected the appropriate cell in the source workbook, then hit ctrl-shift-enter. I now have linked cells.
Because I have several ‘source’ workbooks to retrieve data from, I would rather copy and paste the filename (including path and sheet info) into my destination workbook and then use that as a reference for the corresponding formulas. Something similar to the INDIRECT function. INDIRECT works but only if the source file is open. I tried concatenate but the result is a string, not a formula.
Example of using concatenate:
[TABLE="width: 160"]
<tbody>[TR]
[TD="width: 160"]C:\Users\spanders\Desktop\CostIt\White River Basin-Cost Est\[NF White River 2018 CostIt v1.0.xlsm]SUMMARY'!$E$3
[/TD]
[/TR]
</tbody>[/TABLE]
I’ve spent hours trying to solve this problem. Any suggestions?
Thx,
