Dynamic Worksheet Name in a Link

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

First--Thanks to Frank Kabel for his previous help.

I need to use a variable in a worksheet/cell reference to another workbook/worksheet. I have an example, shown below, that almost does what I need.I need to automatically access the previous day's worksheet for my calculations. The following example does this quite well, but it does not adjust for the month in the Workbook name. Can I acoomplish this???

=INDIRECT("'[Well and Pump RunJuly.xls]"&TEXT(TODAY()-1,"DD")&"'!$Z$37")

Please note that the lack of a space between "Run" and "July" is intentional. I need to be able to update the "July" portion ot the workbook name to the current month automatically like the date. I tried utilizing the TEXT(TODAY(),"MMMM" command without success.

Any help is GREATLY appreciated. The boss is on my back to make this happen. I have been working on this project for 4 months to tie our SCADA system to Excel through a program called XLReporter. This is the absolute last piece of the puzzle. Everything else is working.

--Al
 
Hi
note: the following works ONLY if the workbook is OPEN!. If the
workbook is closed you'll get an error:
=INDIRECT("'[Well and Pump Run" & TEXT(TODAY(),"MMMM") &
".xls]"&TEXT(TODAY()-1,"DD")&"'!$Z$37")

If the other workbook is closed try the following: have a look at the
Add-In MOREFUNC.XLL(http://longre.free.fr/english)

use the function INDIRECT.EXT.e.g.use:
=INDIRECT.EXT("'[Well and Pump Run" & TEXT(TODAY(),"MMMM") &
".xls]"&TEXT(TODAY()-1,"DD")&"'!$Z$37")


you may also have a look at the following thread (describing further
alternatives for accessing closed workbooks): http://tinyurl.com/2c62u
 
Back
Top