help with concatenate function

mehtarm

New Member
Joined
Sep 15, 2006
Messages
4
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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
=indirect(CONCATENATE("'C:\XYZ\Books\New MSS Tracker\[Niles_Wk_",$BQ23,".xls]volume'!$B$15") )

HTH
Cal
 

mehtarm

New Member
Joined
Sep 15, 2006
Messages
4
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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
 

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top