Using Indirect Concatenate to get values from other workbook

[bc4240]

I have an question about using a combo box value in a formula to look up information in one of six workbooks.

I have a combo box that contains a list of years:

2009
2010
2011
2012
etc

I have 6 and eventually more workbooks named like so:

PStop Break Down 2010 00.xls
PStop Break Down 2010 56.xls
etc

I would like to bring data from a particular column in one of these workbooks into a workbook and there I will use the values for calculations. Currently I am using this formula to load the data:

" 'C:\Calculator\[PStop Break Down 2010 00.xls]Total'!FR1 "

but this formula fixes the year and I need the year to be dynamic


I was thinking I could do something like this:


" indirect(concatenate("'C:\Calculator\[PStop Break Down"," ",$C$12," ", "00.xls]Total'!FR1")) "


$C$12 = the combo box year value

But it doesn't work I only get a "REF" return.

currently I'm using this in 2003

any suggestions would be appreciated

BC

 

[Osvaldo Palmeiro]

try

=INDIRECT("'C:\Calculator\[PStop Break Down "&$C$12&" 00.xls]Total'!FR1")

 

[bc4240]

Thanks I'll give it a try

 

[bc4240]

Sorry it only returns a REF

 

[barry houdini]

INDIRECT only works with open workbooks....is the source workbook open? If you want to extract from a closed workbook then you might be able to install Morefunc add-in and use INDIRECT.EXT function (try googling Morefunc)

 

[bc4240]

workbook was closed and will have to be closed sometimes I'll look at MoreFunc thanks for advice