help with concatenate function

[mehtarm]

hi,

I am trying to use the following function :

= CONCATENATE("'C:\XYZ\Books\New MSS Tracker\[Niles_Wk_",$BQ23,".xls]volume'!$B$15")

Essentially , I need to display the value from another excel book (Niles_Wk_19, Niles_Wk_20 ...) ..

I am getting an out put as a text - 'C:\XYZ\Books\New MSS Tracker\[Niles_Wk_19.xls]volume'!$B$15 as the value in the cell BQ23 is 19.

I want the actual value from the excel sheet volume which is in the excel book Niles_Wk_19.xls.

If I just type in a cell as ='C:\XYZ\Books\New MSS Tracker\[Niles_Wk_19.xls]volume'!$B$15' , I do get the correct value but this is very cumbersome to do as there are tons of rows.

Any ideas?

 

[Cbrine]

=indirect(CONCATENATE("'C:\XYZ\Books\New MSS Tracker\[Niles_Wk_",$BQ23,".xls]volume'!$B$15") )

HTH
Cal

 

[mehtarm]

help with concatenate

The INDIRECT works only if the corresponding excel files are open. Once you close all the files and repopen the source file, the values appear as #Ref

 

[Cbrine]

mehtram,
I didn't realize that indirect had this limitation. I've tried a few other things and I was not able to come up with a solution.

I could whip up something in vba using a UDF to do this, if you are interested.

Let me know.

Cal