Can INDIRECT do this?

[NewburyK]

{Windows XP, Excel 2003}

I have been searching for a few hours, but have not come across the information that i need.

My manager has decided that we need a Metrics board of all of our critical numbers posted outside of the door to our department so that the president can see them when he walks by. Currently all those numbers are spread out on various worksheets on different workbooks.

One of those workbooks (currently called Q2.xls) has a new sheet added weekly for that week's schedule, each named the date of the Monday of that week (ex: this weeks is "Jun25"). I have a cell (H1) in my new workbook where I write the Monday date, and i want to reference a cell in the current week's sheet on this other book.

The direct code to the cell is

=[Q2.xls]Jun25!$BO$31

I'm wondering if i can use INDIRECT to replace "Jun25" with a reference to H1. This code is going to be added to a Macro once I can get it to work, so if there is a VBA code to make this easier, I am open to that too (just note that I am a complete VBA noob, just beginning Macros)

Thank you for your time
-Kevin

 

[JMonty]

If you're not using INDIRECT to reference the name of the file and only the Jun25, then I believe it is possible. INDIRECT only has issues with another workbook if you reference the name/file path of the workbook using a cell content. It requires that workbook to be open. But if it's just a section of the sheet itself, then I believe it is.

Do let me know if this is the case with your attempt.

 

[Sal Paradise]

I would save yourself a lot of headaches and create a new sheet in that Q2 book called "ThisWeek" and just have it always reference the latest information in the workbook (use the indirect formula as JMonty suggested in that book). Then just reference the "ThisWeek" sheet from that book in the new sheet to save yourself the trouble.

 

[NewburyK]

Thanks for the replies guys, but I actually figured a different way to get this all to work. I just had to get my frame of mind into coding VBA, rather than having a Macro execute an Excel command. I defined a Dim String to point to the cell where I write the week (Jun25 in the example), then use that variable to open the sheet in the other workbook. Worked much easier than trying to get my macro to run the INDIRECT code.