Help needed from Mr. Excel and others

[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?

 

[Greg Truby]

Welcome to the board. Is it okay if I answer, or did you want to wait around for Bill (Mr.Excel) to answer this personally?

<ul>[*]=Indirect("'C:\XYZ\Books\New MSS Tracker\[Niles_Wk_" & $BQ23 &".xls]volume'!$B$15") [/list]

 

[mehtarm]

help needed

There is an issue with INDIRECT function as the value disappears if you close all the files and reopen the source file.

Can you try comething else?

Thx
s

 

[Greg Truby]

I'm taking what you say on faith as I haven't time to test that. You might see if you can adapt this:

http://www.j-walk.com/ss/excel/tips/tip82.htm

 

[Oaktree]

Another option: INDIRECT requires the source file to be open (or you'll get #REF! errors), but INDIRECT.EXT in the morefunc add-in does not.