want to change the workbook link based on a text

[concol]

I have a workbook (Report) that is linked to another workbook (Yahoo Stock Quote) ...

My (Yahoo Stock Quote) workbook has multiple tabs.

I want to be able to link my workbook (Report) to Yahoo Stock Quote based on the tab I am on.

For example

=+'C:\Conrad\STOCKS\[Yahoo Stock Quote Downloader_ver1.xlsm]BMO'!A16 ----

I want to change the "BMO" to another name -- based on what sheet I am on in Report

So if I am on sheet "BMO" in report -- it pulls from tab "BMO" from the Yahoo Stock Quote file

If I am on Sheet DIS - on the Report work book -- I want the Data to pull from "DIS" from the Yahoo Stock Quote file.


I know I can use things like "find replace" but I have over 50 tabs and the names can change based on what stocks I am tracking.

I guess what I am really asking is there a way to make "BMO" variable based on the current sheet name.

Thanks for the help.

 

[Scott T]

Something like this should work as long as the other workbook is open, Indirect does not work on closed workbooks. If you are on BMO in report then it will pull from BMO on the other workbook. Change file names and paths to match your data.

=INDIRECT("[Book2.xlsx]"&RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))&"!$B$7")

 

[concol]

Something like this should work as long as the other workbook is open, Indirect does not work on closed workbooks. If you are on BMO in report then it will pull from BMO on the other workbook. Change file names and paths to match your data.

=INDIRECT("[Book2.xlsx]"&RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))&"!$B$7")

Thanks ... this is almost there .... it works perfectly when the main file is NOT saved -- meaning that the file is just call "Book2" as soon as I save the file and it become "Book2.xlsx" it does seem to work.

Not sure how to explain this or why it is happening

 

[concol]

Thanks ... this is almost there .... it works perfectly when the main file is NOT saved -- meaning that the file is just call "Book2" as soon as I save the file and it become "Book2.xlsx" it does seem to work.

Not sure how to explain this or why it is happening

ok ... THANKS .... My issues is NOT what is above ... my issues was that I had "spaces" in my main file name -- once I removed the spaces it worked perfectly ...

thank you very much for your help -- I could not have even remotely figured out how to do this without your help.