Help needed from Mr. Excel and others

M

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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
How to count all unique values based on another column
Replies
6
Views
3K
Fluff
Fluff
amadams
  • Question Question
Macro to AutoSave Excel Spreadsheets Using Different File Names
Replies
1
Views
449
NdNoviceHlp
NdNoviceHlp
A
  • Solved
Extract and Merge Files
Replies
1
Views
112
rowebca
rowebca
M
  • Question Question
I need help with VBA/macro script to collect user input and fill cells
Replies
4
Views
511
mumps
mumps
K
  • Question Question
Modify COUNTIF Formula to count inside parenthesis
Replies
7
Views
204
Fluff
Fluff

Forum statistics

Threads
1,223,964
Messages
6,175,658
Members
452,664
Latest member
alpserbetli

Share this page

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
Back
Top