Help needed from Mr. Excel and others

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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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? :LOL:

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

mehtarm

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

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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.
 

Forum statistics

Threads
1,136,925
Messages
5,678,602
Members
419,774
Latest member
MooseWinooski

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